Skip to Main Content
  • Questions
  • Left Joining Four Tables without duplicates from right tables or Cartesian product!

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Mohammad.

Asked: December 01, 2019 - 5:38 pm UTC

Answered by: Chris Saxon - Last updated: December 05, 2019 - 5:12 pm UTC

Category: PL/SQL - Version: 11g

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: AskTom TV

You Asked

I am running the query below to get data from 4 tables, but the problem that data is fetched as Cartesian product, in other words, items from tblEdu is being duplicated with items from tblTrain


SELECT tblpersonal.*, tbltrain.*, tbledu.*, tblrequests.intReqId
FROM tblpersonal
LEFT JOIN tblrequests ON tblpersonal.intCompNo = tblrequests.intCompNo
AND tblrequests.intReqId = (SELECT MAX(req.intReqId) FROM tblrequests AS req WHERE req.intCompNo = tblpersonal.intCompNo)
LEFT JOIN tbltrain ON tblpersonal.intCompNo = tbltrain.intCompNo
LEFT JOIN tbledu ON tblpersonal.intCompNo = tbledu.intCompNo
WHERE tblrequests.intReqId IS NOT NULL
AND tblpersonal.intCompNo = 12368
GROUP BY tblpersonal.intCompNo, tbltrain.intTrainId, tbledu.intEduId
ORDER BY tblpersonal.intCompNo, tbledu.intEduId, tbltrain.intTrainid;




and we said...

It's hard to say what the issue is without example data.

But you're only joining tbltrain and tbledu on intCompNo. Which maps back to tblpersonal.

So at a guess, you need to add more join criteria between these tables!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.