mysql - get details from 2 tables with 1 query.

Question

Hi all!

I have 2 mysql tables:

The first table table1:

| ID | NAME   |
| 1  | cat    |
| 2  | mouse  |
| 3  | mouse  |
| 4  | mouse  |
| 5  | mouse  |
| 6  | mouse  |

The secound table table2:

| ID | NAME_NA   |
| 1  | cat       |
| 2  | mouse     |
| 3  | cd_rom    |
| 4  | dvd_rw    |
| 5  | house     |

And i want to output this :

mouse    -   5 entries
cat      -   1 entry
cd_rom   -   0 entries
dvd_rw   -   0 entries
house    -   0 entries

Answer 1

Use a LEFT JOIN:

   SELECT x.name_na AS name,
          COALESCE(COUNT(y.name), 0) AS num_entries
     FROM TABLE2 x
LEFT JOIN TABLE1 y ON y.name = x.name_na

...to get:

name     num_entries
--------------------
mouse    5 
cat      1
cd_rom   0
dvd_rw   0
house    0

Answer 2

SELECT `table1`.`NAME`, IFNULL(COUNT(*), 0) as entries FROM
`table2` LEFT JOIN `table1` ON `table1`.`NAME` = `table2`.`NAME_NA`
GROUP BY `table1`.`NAME`;

The LEFT JOIN creates rows for each item in the "left" table (table2 here) even if there's no match in table1, and also will create multiple rows for an item in table2 if there are multiple matching rows in table1 for that item.

Then if you GROUP BY the name and use a COUNT() function, you can get the count of how many matches each item in table2 has in table1.

The IFNULL() is utilized because COUNT() will return NULL if there weren't any matches for that item in table1, so we substitute in 0 instead.