Joseph Santoro Joseph Santoro - 4 months ago 8
SQL Question

Outputs from referencing two tables

EID NAME SALARY DNAME HIRE_DATE NUM_PHONES
--- ------------------ ---------- ---------------------- --------- ----------
111 Yo-yo Ma 80000 Software 01-MAY-81 1
222 Leonard Bernstein 80000 Information Technology 02-JUN-82 2
333 Leonard Bernstein 110000 Research 03-JUL-83 1
444 Yehudi Menuhin 60000 Software 02-JAN-91 3
555 Lata Mangeshkar 50000 Software 01-MAY-81 5
666 Bing Crosby 65000 Tech Support 02-JUN-82 2
777 Frank Sinatra 40000 Tech Support 04-APR-84

DNAME BUDGET EXPENSE REGION
---------------------- ---------- ---------- ----------
Software 800000 600000 Eastern
Tech Support 2000000 3000000 Eastern
Information Technology 3000000 3000000 Western
Research 100000 60000 Western

EID PHONENUM
--- ----------
111 2372918458
222 5051734849
222 5755851934
333 1513664917
444 2206315710
444 3321045029
444 5678540401
444 5752658931
555 2546768967
555 5756390131
555 7201014829

EID PHONENUM
--- ----------
555 7595120111
555 7597209076
666 1513664917
666 5053241942
777 5753241942


Find the ID and name of employees with at least one phone in 575 area code but none in the 505 area code.

I've tried the below action and can't get around the dual eid attributes. Please help!

SQL> select eid, phonenum, name
2 from phone, emp
3 where emp.eid = phone.eid and
4 phonenum like '575%'
5 and not like '505%'
6 ;
select eid, phonenum, name
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Answer

The phone numbers starting with 575 or 505 are in different rows, so it's not as simple as applying two LIKE conditions to the same row. Solution is creating subqueries and check if what we're looking for exists using a condition and does not using the other.

SELECT DISTINCT e.eid, e.name
FROM emp e
WHERE EXISTS(SELECT * FROM phone p1 WHERE p1.eid = e.eid AND p1.phonenum LIKE '575%')
AND NOT EXISTS(SELECT * FROM phone p2 WHERE p2.eid = e.eid AND p2.phonenum LIKE '505%')
Comments