MySQL query, problem with my join?

0
votes

I'am not sure that while problem, but take a look:

I have 3 tables:

  1. Categories: id, icon, name;
  2. Subcategories: id, name, description, category_id;
  3. Topics: id, title, text, author, created, subcategory_id;

Now I'm trying get this info(query):

  $String = "SELECT
  categories.id AS catid,
  categories.icon AS caticon,
  categories.name AS catname,
  subcategories.id AS scatid,
  subcategories.name AS scatname,
  subcategories.description AS scatdescription,
  subcategories.category_id AS scatcatid,
  COUNT(topics.id) AS tid,
  topics.title AS ttitle,
  topics.author AS tauthor,
  topics.created AS tcreated
       FROM
  categories
LEFT JOIN
  subcategories
ON
  subcategories.category_id = 1
LEFT JOIN
  topics
ON
  subcategories.id = topics.subcategory_id
GROUP BY
 categories.id";

Result:

Categories from 5 showing 5 - OK, Subcategories from 4 showing only 1 in first categories.

Maybe the query is too long? Thanks for any answer.

1
mysql
on the first join, you have written: subcategories.category_id = 1 ... this, as I understood, will join on all subcategories of category 1 only ... – sikas

1 Answers

2
votes

this

LEFT JOIN
  subcategories
ON
  subcategories.category_id = 1

should be this

LEFT JOIN
  subcategories
ON
  subcategories.category_id = categories.id