SQL Statement

Quote:

---

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');