Mysql display records even no result

Question

I have two tables namely: listings and tags. These are sample records:

Listings Table

Tags Table

This is my sample SQL query:

SELECT l.tag,
count(case when l.type = 'Consumer' then l.type = '' end) as consumer,
count(case when l.type = 'Supplier' then l.type = '' end) as supplier
                          FROM listings AS l
                          LEFT JOIN tags AS t
                          ON l.tag = t.tag
                          GROUP BY t.tag, l.type

But it displays like this:

I want something like this:

Any ideas how to do it? I would gladly appreciate any kind of help. Thank you.

UPDATED: Changed to:

SELECT l.tag,
count(case when l.type = 'Consumer' then l.type = '' end) as consumer,
count(case when l.type = 'Supplier' then l.type = '' end) as supplier
                          FROM listings AS l
                          LEFT JOIN tags AS t
                          ON l.tag = t.tag
                          GROUP BY t.tag, l.type

And this is the result:

OUTER keyword seems working however I need to display still the AZ with 0 and 0 for Consumer and Supplier.

Possible duplicate of [SQL how to do an outer join properly](https://stackoverflow.com/questions/44544462/sql-how-to-do-an-outer-join-properly) — Laposhasú Acsa, Jun 19 '17 at 09:10
you have `select distinct l.tag` and `group by t.tag`, *and* `l.tag = t.tag` join. so in effect you're asking it to show distinct units of a unique column Get rid of the distinct. — Martin, Jun 19 '17 at 09:11
Group by `l.tag` rather than `t.tag` I suspect this wont help but it keeps all the workings on the `listings` table rather than across both tables? — Martin, Jun 19 '17 at 09:16
@bEtTyBarnes remove l.type from group by and try to run the query. — Rohit Gaikwad, Jun 19 '17 at 09:18
@RohitGaikwad thanks for your reply. I removed l.type however, it just displays 4 records instead of 5 records. It should display AZ | 0 | 0 — , Jun 19 '17 at 09:20
@LaposhasúAcsa I tried copying the sql from the link you added, however it doesn't display 0 records. — , Jun 19 '17 at 09:22

score 1 · Answer 1 · edited Jun 19 '17 at 10:12

1

Why are you using join for getting this when you have saved tag name itself instead of ids in listings table.

You can use this query

SELECT tag,
SUM(CASE when type = 'Consumer' then 1 else 0 END) AS Consumer,
SUM(CASE when type = 'Supplier' then 1 else 0 END) AS Supplier 
FROM `listings` group by tag

edited Jun 19 '17 at 10:12

Martin

22,212
11
70
132

answered Jun 19 '17 at 09:23

Passionate Coder

7,154
2
19
44

Thanks for this. There is a reason why I need to join the table (client's requirement) Your query seems to work however it doesn't display AZ with 0 and 0 for Consumer and Supplier – Jun 19 '17 at 09:25

score 1 · Accepted Answer · edited Jun 19 '17 at 09:26

1

Try this...........

SELECT t.tag,
count(case when l.type = 'Consumer' then l.type = '' end) as consumer,
count(case when l.type = 'Supplier' then l.type = '' end) as supplier
                          FROM listings AS l
                          RIGHT JOIN tags AS t
                          ON l.tag = t.tag
                          GROUP BY t.tag

edited Jun 19 '17 at 09:26

answered Jun 19 '17 at 09:25

Rohit Gaikwad

817
2
8
24

Wow! This seems to be working on my end! RIGHT keyword is the right way to go! – Jun 19 '17 at 09:26
1

@bEtTyBarnes https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins – Martin Jun 19 '17 at 10:35

Mysql display records even no result

2 Answers2