cannot get parents records which do not exist in child record using 'leftjoin'

Question

cannot get parents records which do not exist in child record using 'leftjoin'

dua_anshu

SSC Veteran

Points: 220
More actions
August 25, 2008 at 9:52 am

#192796

i am trying to get distinct records from order table (child table) associated
with employee table (parent table) based on the latest 'Id' field of child table-
select x.Oid, x.employeeID, o.changedate,e.followupdate,o.date from
(select max(o.id) as oId,eid from order o
left join employee e on e.id = o.employeeid
group by eid) x inner join order o on o.id=x.oid
order by x.eid
But the above query does not return the records which exists in parent table but not in child table.
Any suggestions ? what i am missing in my query?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's just your INNER JOIN at the very end that is exluding the records you were looking for; simply change to this i think:

[font="Courier New"]

SELECT

x.Oid,

x.employeeID,

o.changedate,

e.followupdate,

o.date

FROM

(SELECT

MAX(o.id) AS oId,

eid

FROM ORDER o

LEFT JOIN employee e ON e.id = o.employeeid

GROUP BY eid) x

LEFT OUTER JOIN ORDER o ON o.id = x.oid

ORDER BY x.eid[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dua_anshu SSC Veteran Points: 220 More actions · Answer 2

It does not help as there are 2000 reocrds in parent table but if use the query as corrected by i get only 1250 records.

please suggest.

Lowell SSC Guru Points: 323518 More actions · Answer 3

maybe you need to filter so you get JUST the parent records?

slight change by adding a WEHRE clause, and changing which Oid to select like this?:

[font="Courier New"]

SELECT

o.Oid,

x.employeeID,

o.changedate,

e.followupdate,

o.date

FROM

(SELECT

MAX(o.id) AS oId,

eid

FROM ORDER o

LEFT JOIN employee e ON e.id = o.employeeid

GROUP BY eid) x

LEFT OUTER JOIN ORDER o ON o.id = x.oid

WHERE X.Oid IS NULL

ORDER BY x.eid

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dua_anshu SSC Veteran Points: 220 More actions · Answer 4

i ran that query but now it return no records at all.

The problem is that the subquery -

SELECT
MAX(o.id) AS oId,
eid
FROM ORDER o
LEFT JOIN employee e ON e.id = o.employeeid
GROUP BY eid

only returns child records associated with parent records. Left join does not seems to work in this subquery.

please suggest.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

I think you need to change your join. You said you wanted rows from the parent table (employee) that didn't have a record in the child table (order), so I think the way you wrote your FROM clause in the subquery, you'll need to do:

[font="Courier New"]FROM order o

RIGHT JOIN employee e ON e.id = o.employeeid [/font]

The RIGHT JOIN or LEFT JOIN refers to the order of the tables in the FROM clause (order, employee), not the order of the join condition (e.id, o.employeeid)