0

I'm struggling with 3 tables I'm trying to join and exclude certain results. Basically it's almost the same as mysql join where not exists, however I still can't figure out how to combine multiple tables successfully.

The goal is to select results from table A where the row in the intermediate table B is null OR where the table C (linked to B, but not to A) does not match a specific value.

Something like:

SELECT      x 
FROM        tablea
LEFT JOIN   tableb
ON          tablea.x = tableb.x
LEFT JOIN   tablec
ON          tableb.y = tablec.y
WHERE       tableb.x IS NULL
OR          tablec.z != 'excluded'

but it doesn't work. I'd also like to avoid subqueries like using NOT IN or NOT EXISTS in order to speed up things... any suggestions?

EDIT: in spite of what I previously said, it should work. Just remember to double check proper braces, nesting and the 'where' clauses position when merging multiple joins

Community
  • 1
  • 1
3dolab
  • 13
  • 4

2 Answers2

1

This should do the trick with one query and be reasonably fast. :)

SELECT x 
FROM tablea
    LEFT JOIN tableb ON tablea.x = tableb.x
    LEFT JOIN tablec ON tableb.y = tablec.y AND tablec.z != 'excluded'
WHERE
    tableb.x IS NULL
    OR tablec.y IS NOT NULL

This is under the assumption that you don't mind getting rows from tablea where both tableb.x is null and tablec.y is null, or that neither of them are. If you want only rows from tablea where tableb.x OR tablec.y contains values, you need to change the OR to XOR.

mikn
  • 484
  • 3
  • 7
  • thanks a lot! in all effect, I've finally managed to make my query work just by editing the syntax of another previous join (omitted since not relevant to the purpose)... well I had to pay more attention to nested joins and to always put every 'where' clause at the end. Anyhow, the trick you provided is extremely precious, specially about OR & XOR, and after all it confirms that my query wasn't so bad :) – 3dolab Nov 29 '11 at 13:02
0

It's really two separate queries, so use a UNION:

SELECT tablea.*
FROM tablea
LEFT JOIN tableb ON tablea.x = tableb.x
WHERE tableb.x IS NULL
UNION
SELECT tablea.*
FROM tablea
LEFT JOIN tablec ON tableb.y = tablec.y AND tablec.z != 'excluded'

p.s. your life will be easier if you don't format your SQL with that crazy "table name alignment" style. After many years coding SQL, I find this the easiest to read

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thanks, I'll keep in mind your suggestion. Sometimes in PHP scripts I find that alignment useful, that's why I've pasted such a "crazy" code... my apologies :) – 3dolab Nov 29 '11 at 12:50