I need to show all cars from BRAND_CAR even if there is no car in it

Question

I created this query and i can't show all brand car even with 0 vehicule created on vehicule table.

SELECT BRAND_CAR_NAME, COUNT(*)
FROM brand b, vehicule v, model mo
WHERE v.ID_MODEL = mo.ID_MODEL
AND mo.ID_BRAND = b.ID_BRAND
GROUP BY BRAND_CAR_NAME

what it show me :

Brand 1 :   1
Brand 3 :   1 
Brand 4 :   1
Brand 6 :   1

I want to see :

            Brand 1 : 1
            Brand 3 : 1 
            Brand 4 : 1
            Brand 6 : 1
            brand 2 : 0
            brand 5 : 0
            brand 7 : 0

SELECT NOM_MARQUE, IFNULL(v.ID_VOITURE)
FROM marque m, voiture v, model mo
WHERE v.ID_MODEL = mo.ID_MODEL
AND mo.ID_MARQUE = m.ID_MARQUE
GROUP BY NOM_MARQUE

this might help: https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate — Stephan Hogenboom, Jun 18 '19 at 07:21

score 1 · Answer 1 · answered Jun 18 '19 at 07:56

1

Use Left Join to get the result, try the below query

SELECT b.BRAND_CAR_NAME, COUNT(*) 
FROM brand b
LEFT JOIN model mo ON b.ID_BRAND = mo.ID_BRAND
INNER JOIN vehicule v ON mo.ID_MODEL = v.ID_MODEL
GROUP BY b.BRAND_CAR_NAME;

answered Jun 18 '19 at 07:56

James

1,819
2
8
21

Barbaros Özhan · Answer 2 · 2019-06-18T08:46:07.247

1

You can use a LEFT JOIN especially just before vehicule table with SUM( IFNULL(SIGN(v.ID_MODEL),0) )

( I think even better to use a LEFT JOIN before model table also, againct missing records for model table )

SELECT BRAND_CAR_NAME, SUM( IFNULL(SIGN(v.ID_MODEL),0) ) as BRAND_COUNT
  FROM brand b
  LEFT JOIN model mo ON mo.ID_BRAND = b.ID_BRAND
  LEFT JOIN vehicule v ON v.ID_MODEL = mo.ID_MODEL
 GROUP BY BRAND_CAR_NAME;

Demo

edited Jun 18 '19 at 08:46

answered Jun 18 '19 at 08:13

Barbaros Özhan

59,113
10
31
55

With SUM i get the ID in BRAND_COUNT, i got 6 vehicule created with 6 distinct brand, in a list of 20 brand. This query show me almost the good result, the only thing is instead of having a count of how many vehicule of each brand is created, i got the value of ID (ex : Fiat has ID 20, and i got Fiat : 20, Ferrari 18, Ford 10, Mercredes 7, Citreon 5, Peugeot 1, Seat 0, Renaut 0, BMW 0, Wolkwagen 0 ect.. – Kovenk Jun 18 '19 at 08:31
@Kovenk have a look at the Demo, and tell me what's different with your data model please. – Barbaros Özhan Jun 18 '19 at 08:34
And when i replace SUM by COUNT i got wrong values. BWM > 3 and all the other > 1. I don't understand why i got those numbers – Kovenk Jun 18 '19 at 08:39
@Kovenk well, we need one more function : `SIGN()` – Barbaros Özhan Jun 18 '19 at 08:46
But with sign, if i got 2 Fiat, it will return 1? – Kovenk Jun 18 '19 at 08:49
@Kovenk can I see your tables' structure and sample data, preferably on a fiddle site ? – Barbaros Özhan Jun 18 '19 at 08:51
What is a Fiddle site? I have the .sql file but don't know where to share it – Kovenk Jun 18 '19 at 08:58
@Kovenk I meant you can create your tables and populate them within a fiddle site like my Demo, and share by a link with us. – Barbaros Özhan Jun 18 '19 at 09:53

I need to show all cars from BRAND_CAR even if there is no car in it

2 Answers2