CODEWITHSUNDEEP
mysqlsqlselect
user1918956
user1918956

Reputation: 897

How can I join two tables, keeping rows that do not meet the JOIN condition?

ticket                      
+----------+--------+
| ticketID | assign |
+----------+--------+
|     1015 | NULL   |
|     1020 | James  |
|     1021 | Nick   |
+----------+--------+

staffinfo
+---------+-------+
| staffID | staff |
+---------+-------+
|       1 | Jane  |
|       2 | James |
|       3 | Nick  |
|       4 | Cole  |
+---------+-------+

SELECT staff,COUNT(*) as count FROM staffinfo,ticket
WHERE ticket.assign = staffinfo.staff
GROUP BY staff

result:
    +-------+-------+
    | staff | count |
    +-------+-------+
    | James |     1 |
    | Nick  |     1 |
    +-------+-------+

Works fine, but infact i need smthing like:

+-------+-------+
| staff | count |
+-------+-------+
| James |     1 |
| Nick  |     1 |
| Jane  |     0 |
| Cole  |     0 |
+-------+-------+

COUNT doesnt count records that arent in the table, and since i just started learning SQL, i wanna ask if theres a way to count as the above result?

Upvotes: 2

Views: 2742

Answers (2)

Nandu
Nandu

Reputation: 3126

Use LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SELECT staffinfo.staff, count(ticket.assign)
FROM staffinfo
LEFT JOIN ticket
ON ticket.assign =staffinfo.staff
GROUP BY staffinfo.staff

The LEFT JOIN keyword returns all the rows from the left table (staffinfo), even if there are no matches in the right table (ticket).

Upvotes: 0

John Woo
John Woo

Reputation: 263893

you should be using LEFT JOIN

SELECT  a.staff,    COUNT(b.assign) as count 
FROM    staffinfo a
        LEFT JOIN ticket b
            ON b.assign = a.staff
GROUP   BY a.staff

To fully gain knowledge about joins, kindly visit the link below:

Upvotes: 8

Related Questions