Left join with multiple tables and conditions

Question

I want to sort after a value in another table the current table is referenced to. My query looks like this:

SELECT o._id,
       o.titel,
       o.beschreibung
FROM   `objekt` AS o,
       `objekt_einzel` AS oe,
       `objekt_einzel_immobilie` AS oei,
       `objekt_art` AS oa,
       `verortung` AS v
       #here
       ,`person` AS p,
       `person_bauträger` AS pb
       #end
WHERE  o._id = oe.objekt_id
       AND oe._id = oei.objekt_einzel_id
       AND oa._id = o.objekt_art_id
       AND o.ort_id = v._id
       #here
       AND oe.bauträger_id = pb._id
       AND pb.person_id = p._id
       #end
       AND ( oei.justimmo_objekt_id = "0"
              OR oei.justimmo_objekt_id IS NULL
              OR oei.justimmo_objekt_id = "" )
#here
ORDER  BY p.firmenbezeichnung ASC

The query is working fine but it shows me only values if oe.bauträger_id is set. I also want the null values. So I need a left join. I tried different things but I only get messages like unknown column or I get too much results.

I tried to simplify it to this:

SELECT o._id,
       o.titel,
       o.beschreibung
FROM   `objekt` AS o,
       `objekt_einzel` AS oe,
        (SELECT oe.bauträger_id
        FROM objekt o, objekt_einzel oe, objekt_einzel_immobilie oei
        WHERE o._id = oe.objekt_id AND oe._id = oei.objekt_einzel_id) AS menge1
LEFT JOIN
        (SELECT pb._id AS bauträger_id
        FROM person p, person_bauträger pb
        WHERE p._id = pb.person_id) AS menge2
ON menge1.bauträger_id = menge2.bauträger_id
WHERE o._id = oe.objekt_id AND oe.bauträger_id = menge1.bauträger_id

but here I get a too big result set. I don't know how to explain this better. The data sets are too big to create an example. I hope you understand what I mean.

@TonyHopkinson I agree using `JOIN … ON` syntax instead of implicit joining would be generally really useful as it can make things more clear, but in this case it is not absolutely necessary. — Palec, Oct 29 '13 at 22:11
@Palec It's not absolutely necessary to not shoot yourself in the foot either. — Tony Hopkinson, Oct 29 '13 at 22:20
@grabner, as far as I can see you are getting a cartesian product of the first set of inner joins and the outer with person_bautrager. Can't see any way to restrict that in what you've posted. Schema would help. — Tony Hopkinson, Oct 29 '13 at 22:28

score 1 · Accepted Answer · answered Oct 30 '13 at 08:22

1

SELECT o._id,
       o.titel,
       o.beschreibung
FROM   `objekt` AS o
       JOIN `objekt_einzel` AS oe ON o._id = oe.objekt_id
       JOIN `objekt_einzel_immobilie` AS oei ON oe._id = oei.objekt_einzel_id
       JOIN `objekt_art` AS oa ON o.objekt_art_id = oa._id
       JOIN `verortung` AS v ON o.ort_id = v._id
       LEFT JOIN `person_bauträger` AS pb ON oe.bauträger_id = pb._id
       LEFT JOIN `person` AS p ON pb.person_id = p._id
WHERE  oei.justimmo_objekt_id = "0"
       OR oei.justimmo_objekt_id IS NULL
       OR oei.justimmo_objekt_id = ""
ORDER  BY p.firmenbezeichnung ASC

This second try should work as it is just the original code rewritten using JOIN syntax and with LEFT JOINs.

answered Oct 30 '13 at 08:22

Palec

12,743
8
69
138

Thanks! This works! Could you please exlain when I have to use `LEFT JOIN` and `JOIN`? Is there a general rule? I think I need the `JOIN` because otherwise the tables would be unknown. Is in this case the order relevant for the `LEFT JOIN`? – grabner Oct 30 '13 at 20:40
@gabner In SQL, [NULL never equals NULL](http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server). If you join two tables on a column that contains NULLs, such rows are ignored by inner join (`JOIN`, `INNER JOIN`). Left outer join (`LEFT JOIN`, `LEFT OUTER JOIN`) contains rows with NULL key from the first table, right outer join (`RIGHT JOIN`, `RIGHT OUTER JOIN`) from the second one, full outer join (`OUTER JOIN`, `FULL OUTER JOIN`; not implemented in MySQL) from both. Columns not set in the original table are filled with NULLs in such rows. – Palec Nov 01 '13 at 10:58
@gabner See also this question on [differences between SQL joins](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) and an [even better one](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join). Also [examples](http://stackoverflow.com/a/5875572/2157640) could help understanding. Outer joins are generally used when a part of the information is optional and you want the rest of it regardless of its presence. – Palec Nov 01 '13 at 11:22
@gabner The order of joins of any type matters. At least the order of columns in the result depends on it. If performance and the order of columns is not an issue, `table1 LEFT JOIN table2` is equivalent to `table2 RIGHT JOIN table1`, inner join and full outer join stay the same. A sequence of joins is just a relational expression, join is left-associative and as I said non-commutative. – Palec Nov 01 '13 at 11:31

Left join with multiple tables and conditions

1 Answers1