-
I am joining a customer table to a phone number table based on customer_id. a customer can have many numbers, but i want only the primary one. however if there is not a primary one, i just want one of any of the others. this is what i have so far.
SELECT phon.number
FROM phones phon, customers cust
WHERE phon.customer_id (+)= cust.customer_id
AND phon.primary = 'Y';
any suggestions?
-
select
case when col2 = 'Y' then col2
.....
......
end xx
from
(SELECT phon.number col1,phon.primary col2
FROM phones phon, customers cust
WHERE phon.customer_id (+)= cust.customer_id
)
Vijay.s
-
does this only work with 8i? unfortunately i am working with 7i and CASE is not supported.
-
select *
from customers x, phones y
where x.customer_id = y.customer_id and primary = 'Y' union
( select *
from customer x, phones y
where x.customer_id = y.customer_id and primary != 'Y'
and x.customer_id not in ( select x.customer_id
from customers x, phones y
where x.customer_id = y.customer_id and primary = 'Y')
and rownum = 1
)
-
The simple and powerfull solution is DECODE .
Use DECODE
SELECT DECODE (phone.primary,'Y',phon.number,phone.number) ph_number
FROM phones phon, customers cust
WHERE phon.customer_id (+)= cust.customer_id;
-
I thought about DECODE. but it wont work for this situation.
"however if there is not a primary one, i just want one of any of the others ".
Outer join will bring up all the rows, What we need is to randomly pick a row ( for a customer ) for those customers
that dont have a primary phone number.
Am i assuming anything wrong ? ...
-
can u post me how the data is stored..i am confused abt that..
Vijay.s
-
Sorry that the spacing is off...let me know if it makes it confusing.
Phones
customer_id phone_number primary
-------------- ----------------- ---------
123 555-5555 Y
123 333-3333
123 444-4444
456 333-3030 Y
888 454-4403
888 234-4432
Customers
customer_id name
-------------- ------
123 bob
456 jon
888 sue
So i would want 555-5555 for bob, 333-3030 for jon, and either one of the numbers for sue (but just one of them).
-
select
col1,
DECODE((decode(col2,'Y',col3,NULL)),NULL,
(SELECT PHONE_NUMBER FROM PHONES
WHERE CUSTOMER_ID = A.COL1 AND ROWNUM<=1)
,COL3) PHNO
from
(
SELECT phon.customer_id col1,phon.number col2,phone_number col3
FROM phones phon, customers cust
WHERE phon.customer_id (+)= cust.customer_id
) A
Vijay.s
-
i think something in your syntax is wrong. does that run for you?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|