Burki Burki - 1 year ago 76
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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download