1

I have 3 tables, cars, cars_owner and owner. I want to have the complete list of cars, those that do have owners and those that don't.

When a car has an owner, I must have the data of the owner.

Wrong query 1 (selects ownerless cars out):

SELECT * FROM car 
LEFT JOIN cars_owner ON ...
INNER JOIN owner ON ...

Wrong query 2 (selects cars_owner relations without owners too):

SELECT * FROM car 
LEFT JOIN cars_owner ON ...
LEFT JOIN owner ON ...

How do I left join a table with an inner join in MySQL?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Johnny Rhl
  • 19
  • 1
  • 2
  • I want to have all cars - if owned than with the data of owner - if without owner as NULL (as LEFT JOIN naturally does) – Johnny Rhl Feb 12 '13 at 10:02
  • So, the table cars_owner has records without the owner. Does it have one record for each car then? Or are those just records from cars who had an owner someday? – Mateus Schneiders Feb 12 '13 at 10:24
  • 1
    Can a car have more than one owner? if not why do you need 3 tables? You could just have the owners table and a cars table that references the owners table, that would make your query much easier. – Euclides Mulémbwè Feb 12 '13 at 12:47
  • Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad definition/query/function with the obligatory [mre], including why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about bad code 1st because misconceptions get in the way of your goal. And bad code doesn't tell us what you wish it would do. [ask] [Help] – philipxy Jan 15 '23 at 18:41
  • Does this answer your question? [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/questions/55094277/is-it-true-that-using-inner-join-after-any-outer-join-will-essentially-invalidat) – philipxy Jan 15 '23 at 18:42
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Jan 15 '23 at 18:48

2 Answers2

0

This Query returns the cars that have owners:

SELECT * FROM car
LEFT JOIN cars_owner ON (car.CarID=cars_owner.CarID)
inner join owner ON (owner.OwnID = cars_owner.OwnID)

However this returns the cars without owners:

SELECT * FROM car
where (car.CarID!=(SELECT car.CarID FROM car
                   LEFT JOIN cars_owner ON (car.CarID=cars_owner.CarID)
                   inner join owner ON (owner.OwnID = cars_owner.OwnID)) )
Zeina
  • 1,573
  • 2
  • 24
  • 34
0

You just need to nest the JOINs correctly:

SELECT * FROM car 
LEFT JOIN (cars_owner INNER JOIN owner ON cars_owner.owner_id = owner.owner_id
          ) ON car.car_id = cars_owner.car_id

The point is that you want all cars (so a left join on cars_owner), but only cars_owner that have an existing owner (so an inner join on owner).

The brackets make sure that the inner join is executed first. That is: Give me all cars, and from those car_owners that have an actual owner, show the owners (otherwise show them as NULL, but don't discard the whole car row).

BogisW
  • 401
  • 2
  • 16