LEFT JOIN [message #338392] |
Mon, 04 August 2008 17:23  |
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 #338398 is a reply to message #338392] |
Mon, 04 August 2008 17:50  |
 |
BlackSwan
Messages: 26766 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.
|
|
|