SQL Statement

[tron]

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?

[vbaskar]

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
)

[tron]

does this only work with 8i? unfortunately i am working with 7i and CASE is not supported.

[Victoria]

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
)

[aph]

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;

[Victoria]

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 ? ...

[vbaskar]

can u post me how the data is stored..i am confused abt that..

[tron]

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).

[vbaskar]

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

[tron]

i think something in your syntax is wrong. does that run for you?

[sriesha]

Originally posted by tron
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?

Hi tron,
just u try this.

Select a.customer_id,b.number From CUSTOMERS a,PHONE b
where a.customer_id = b.customer_id
and b.primary = 'Y'
union
select a.customer_id,b.number From CUSTOMERS a,PHONE b
where a.customer_id = b.customer_id
and rownum = 1
and a.customer_id in ( Select distinct customer_id PHONE
minus
Select distinct customer_id PHONE
where primary = 'Y');