I've run into a problem I cannot solve in SQL, hope some of you can
help me. First of all, my constraints. I have to resolve all this in a
single SQL statement, as it is to be passed on to another program.
Secondly, I'm restrained to the functionality of MySQL 3.23. Upgrading
is not an option.
Ok, the problem.
I have the following tables with fields:
table0(id0,id1,id2)
table1(id1,id3)
table2(id2,id4)
table1 can contains one or more rows linked to table0 with id1, same
with table2. id3 and id4 has the same semantic meaning.
I need to list out every row in table0. I also (and this is the tricky
part) need to print out a field that indicates (Y/N) if a row in
either table1 or table2 or both exists that contain a certain value in
either id3 or id4 for that certain row in table0. NB, table1 and
table2 can contain many rows with unrealated id3/id4 values.
Listing out all that _do_ have this value is dead easy, the problem
arises when I also need to list out the rest that do not have this
value.
Here is code that lists out all that do have the row, what I need help
with is to also list out the rest and indicate this with a Y/N field.
(Beware of simple mistakes in this code, its just an example)
SELECT table0.id1,table0.id2 FROM table0
LEFT JOIN table1 ON table0.id1 = table1.id1
LEFT JOIN table2 ON table0.id2 = table2.id2
WHERE table1.id3 = '147' OR table2.id4 = '147;
Can somebody please help with this?
--
regards
Stein