Inner Join condition

Question

I have two tables "cats_lables" and "classified_cats", where in "cats_lables" table I can only add a new classes/lables of cats. And then I can use this class/lable to classify new cats and add them to the table "classified cats".

so the tables are like this:

cats_lables: 

id   cats_lable
1     Polydactyl
2     Snowshoe
3     Calico


classified_cats:

id   class_id   cat_age    placeOfBorn   cat_price 
1          1     3 months   Eygipt         1000
2          3     6 months   Lebanon        2000

so, this is the query that shows only the cats that have been classified:

SELECT "cats_lables".*,
        CAST(COUNT("classified_cats"."class_id") AS int) AS class_count
      FROM "Labels"
      INNER JOIN "classified_cats"
        ON "classified_cats"."class_id"="cats_lables"."id"
      GROUP BY "cats_lables"."id"
      ORDER BY class_count DESC

But I want to upgrade my query to show also the cat class even if there is no cat assigned to it.

how can I fix my query?

Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — wp78de, Nov 03 '20 at 04:25

score 0 · Answer 1 · answered Nov 03 '20 at 04:28

A left join should do it for you.

 SELECT "cats_lables",
        CAST(COUNT("classified_cats"."class_id") AS int) AS class_count
   FROM "Labels"
  LEFT JOIN "classified_cats"
     ON "classified_cats"."class_id"="cats_lables"."id"
  GROUP BY "cats_lables"."id"
  ORDER BY class_count DESC

score 0 · Answer 2 · answered Nov 03 '20 at 12:52

I would recommend writing the query like this:

SELECT l.*, COUNT(cc."class_id") AS class_count
FROM "Labels" l LEFT JOIN
     "classified_cats" cc
     ON cc."class_id" = l."id"
GROUP BY l."id"
ORDER BY class_count DESC;

Notes:

"cats_labels" is not in the FROM clause. I am assuming you mean `"Labels".
I strongly encourage you to never use double quotes on identifiers. They just make queries harder to write and to read.
Table aliases make the query easier to write and to read.
You don't specify your database, but not all databases support aggregating by a primary key and then selecting other columns from the table -- although that is permitted in the standard.

Inner Join condition

2 Answers2