Home » SQL & PL/SQL » SQL & PL/SQL » LEFT JOIN
LEFT JOIN [message #338392] Mon, 04 August 2008 17:23 Go to next message
ada26
Messages: 15
Registered: September 2007
Location: cleveland
Junior Member
I have a query that should return a SINGLE row. My table relationship is always one is to many. If I have one table in my WHERE clause that has 3 records, how can I output just 1 record

eg:

EMAIL TABLE
EMP_ID NAME EMAIL CODE
12 DAN DAN@yahoo.com Personal
12 DAN DAN@ABC.com Business



SELECT empno, ename, lname, deptno, salary
FROM EMP,
LEFT JOIN DETP ON dept.emp_id = emp.emp_id
LEFT JOIN SAL ON sal.emp_id = emp.emp_id
LEFT JOIN EMAIL ON email.emp_id = emp.emp_id
WHERE EMP_ID = 12

This query would return me 2 records, since EMAIL table has 2 records. I tried using LEFT OUTER JOIN however I still get the same result. What join condition would give me 1 record?


Thanks!
Re: LEFT JOIN [message #338393 is a reply to message #338392] Mon, 04 August 2008 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


Will any random row be OK?
Re: LEFT JOIN [message #338397 is a reply to message #338392] Mon, 04 August 2008 17:44 Go to previous messageGo to next message
ada26
Messages: 15
Registered: September 2007
Location: cleveland
Junior Member
yes, no problem with the row ordering. Thanks!
Re: LEFT JOIN [message #338398 is a reply to message #338392] Mon, 04 August 2008 17:50 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
There is no "row ordering" when only 1 row is in the result set.

WHERE ROWNUM = 1

The design is flawed if you have to resort to such drastic measures.
Previous Topic: how much space for records
Next Topic: Loop Through String Variable
Goto Forum:
  


Current Time: Sun Dec 04 20:21:08 CST 2016

Total time taken to generate the page: 0.08133 seconds