Burki Burki - 7 months ago 41
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!


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

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