MySQL query not displaying all data when using WHERE clause

Question

I am having some trouble fetching exact data from my database. I don't know what to do to achieve what I am looking for. I need some help in here. Here what I am facing...

Table name: ambulance

+----+---------+
| ID | CONTACT |
+----+---------+
| 01 | 1234567 |
| 02 | 2345678 |
| 03 | 3456789 |
| 04 | 4567891 |
+----+---------+

Table name: category

+----+----------+
| ID | CATEGORY |
+----+----------+
| 01 |    AC    |
| 02 |  Non AC  |
+----+----------+

Table name: multi_category

+----+--------------+-------------+
| ID | AMBULANCE_ID | CATEGORY_ID |
+----+--------------+-------------+
| 01 |      01      |      01     |
| 02 |      01      |      02     |
| 03 |      02      |      01     |
| 04 |      02      |      02     |
| 05 |      03      |      01     |
| 06 |      04      |      02     |
+----+--------------+-------------+

These are my table structures.

And now when I am applying this query

SELECT
    amb.ID,
    amb.CONTACT,
    GROUP_CONCAT(DISTINCT cat.CATEGORY SEPARATOR ', ') AS CATG
FROM
    ambulance as amb
LEFT JOIN multi_category as mct ON
    amb.ID = mct.AMBULANCE_ID
LEFT JOIN category as cat ON
    mct.CATEGORY_ID = cat.ID
GROUP BY
    amb.ID
DESC

The result I am getting is completely okay and the result is...

+----+---------+------------+
| ID | CONTACT |    CATG    |
+----+---------+------------+
| 01 | 1234567 | AC, Non AC |
| 02 | 2345678 | AC, Non AC |
| 03 | 3456789 |     AC     |
| 04 | 4567891 |   Non AC   |
+----+---------+------------+

But when I am applying this query from below

SELECT
    amb.ID,
    amb.CONTACT,
    GROUP_CONCAT(DISTINCT cat.CATEGORY SEPARATOR ', ') AS CATG
FROM
    ambulance as amb
LEFT JOIN multi_category as mct ON
    amb.ID = mct.AMBULANCE_ID
LEFT JOIN category as cat ON
    mct.CATEGORY_ID = cat.ID
WHERE
    cat.ID = 01
GROUP BY
    amb.ID
DESC

I am getting this result..

+----+---------+------------+
| ID | CONTACT |    CATG    |
+----+---------+------------+
| 01 | 1234567 |     AC     |
| 02 | 2345678 |     AC     |
| 03 | 3456789 |     AC     |
+----+---------+------------+

You can see in the CATG field Non AC is missing. But ID - 01 & 02 ambulance are both AC & Non AC.

Now my question is, how do I bring back Non AC in the CATG field with the second query with a WHERE Claus?

Thanks in advance.

LEFT JOIN with WHERE on one of it's columns doesn't make sense. Did you mean LEFT JOIN...AND... or did you want an INNER JOIN? — Charlieface
– Charlieface, Commented Jan 31, 2021 at 7:37
@Bearded because you are filtering by category=01 which is AC category — Abhishek
– Abhishek, Commented Jan 31, 2021 at 7:45
@Strawberry: changing where to and would generate null category for ambulance 4 — Abhishek
– Abhishek, Commented Jan 31, 2021 at 7:53
I want Non AC too while filtering with AC in the CATG field. You can see I am getting 3 sets of result from the second query but 2 of them are AC and Non AC ambulance and only one of them is AC. @AbhishekGinani — Bearded
– Bearded, Commented Jan 31, 2021 at 7:58

Bearded · Accepted Answer · 2021-01-31 10:05:29Z

1

I believe something like this will do the job for you, as you want to retrieve all the categories for an ambulance id as long as at least one of the categories match.

SELECT DISTINCT 
    amb.ID,
    amb.CONTACT,
    T.CATG
FROM
    ambulance as amb
LEFT JOIN multi_category as mct ON
    amb.ID = mct.AMBULANCE_ID
LEFT JOIN
(SELECT mct.AMBULANCE_ID, 
GROUP_CONCAT(DISTINCT cat.CATEGORY SEPARATOR ', ') AS CATG
FROM multi_category as mct 
LEFT JOIN category as cat ON
    mct.CATEGORY_ID = cat.ID
GROUP BY mct.AMBULANCE_ID) T
ON T.AMBULANCE_ID = amb.ID
WHERE
    mct.CATEGORY_ID = 01
ORDER BY
    amb.ID
DESC

edited Jan 31, 2021 at 10:05

Bearded

4511 bronze badges

answered Jan 31, 2021 at 8:04

Neo

8096 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bearded Over a year ago

:) Perfect. It's working. Thank you very much.

detzu · Accepted Answer · 2021-01-31 08:18:56Z

0

In the second select you have the where clause

WHERE
    cat.ID = 01

this is limiting the result set to only AC

If you want to get both AC and Non AC remove the where clause.

answered Jan 31, 2021 at 8:18

detzu

7566 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL query not displaying all data when using WHERE clause

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related