Burki Burki - 19 days ago 6
MySQL Question

MYSQL Join: find all matches from table a , even if not present in table b

i have a table a:

id | name | ...
1 paul
2 paula


and a table b

id | nameId | active
1 1 1


And try to come up with a query that would result in the following:

id | name | active
1 paul 1
2 paula


Nevermind the fact that the example data does not make much sense please.

My problem is that i need a query that selects all records from table a that match some condition, and finds a certain value for those results in table B, if present, and adds it to the result set, but without omitting those results from table a that have no match in table b.

so far i have tried something like this:

SELECT a.id, a.name, b.active FROM a left join b b.nameId=a.id WHERE (something)


But that gives me only the first row.

Any help would be much appreciated!

Answer

Your query should give the desired result, at least minus the mystery WHERE clause:

SELECT a.id,
       a.name,
       COALESCE(CAST(b.active AS CHAR(50)), 'NA')   -- replace NULL with NA
FROM a
LEFT JOIN b
    ON a.id = b.nameId
-- WHERE (you can add a WHERE clause, but it might filter off rows)