I have a problem setting up a SQL Query, hoping someone can help me.
So here's the task, I have two tables I would like to get with a single query. Not a big problem, unless it comes to WHERE clause matching. I need all entries from Table A, but only matching entries from Table B, however keeping entries from Table A where the linked ID is not existing.
To make clear what I have here is an example structure for the Tables...
TABLE A
ID | VAL1 | VAL2 | VAL3
1 | abc | xyz | 123
2 | abc | xyz | 123
3 | abc | xyz | 123
4 | abc | xyz | 123
TABLE B
ID | A-ID | X1 | X2 | X3 | FLAG
1 | 1 | ab | xy | 98 | 1
2 | 1 | ab | xy | 98 | 1
3 | 1 | ab | xy | 98 | 0
4 | 2 | ab | xy | 98 | 1
5 | 2 | ab | xy | 98 | 0
6 | 4 | ab | xy | 98 | 1
So if use this Query...
SELECT a.*, b.* FROM Table_A AS a LEFT JOIN Table_B AS b ON b.a-id = a.id WHERE b.flag = 0
... I get of course only the entries of A that have the matches in B, which would be ID 1 and 2 in this example, because 3 has no entry in B and 4 only an entry with FLAG 1.
However, in the Result-Array, I would need A3 and A4 as well, with the B Array-Values simply to be empty.
I have currenlty no clue if this can be done easily and in a single Query. I already tried a different approach by changing the query to something like...
SELECT a.*, (SELECT b.* FROM Table_B AS b WHERE b.a-id = a.id) AS array FROM Table_A AS a
... but in this case b.* is not allowed. :(
Thanks Pat for your suggestion, I just found a solution that is working for me in this case, so for anyone who might be interested, I moved the WHERE clause to the ON clause and now I get the result I needed...
SELECT a.*, b.* FROM Table_A AS a LEFT JOIN Table_B AS b ON (b.a-id = a.id AND b.flag = 0)
Need to keep that in mind next time. :)