MySQL Join with 3 tables and empty rows

Question

I'm giving up for now with my JOIN creating a view for search reasons - I need help :/

Here are my tables:

Profiles

id   company    user_id
1    ACME       2
2    Joe        4
3    Wolf       5

Users

id   role_id   online
2    4         2010-10-08
4    2         2010-10-08
5    4         2010-10-08

Rubrics

id    title
1     Steel
2     Stone
3     Wood

Profiles_Rubrics

profile_id   rubric_id
1            1
1            2
2            3
2            1

What I want to get from these tables is a view with one row for each profile - also including the profiles that have no entries in the HABTM Profiles_Rubrics. For now I just can get the profiles which have entries in the HABTM table:

CREATE OR REPLACE VIEW Catalog_Branches AS
SELECT
  profiles.id,
  profiles.company,
  GROUP_CONCAT(DISTINCT CAST(rubrics.id AS CHAR) SEPARATOR ', ') AS rubric,
  GROUP_CONCAT(DISTINCT CAST(rubrics.title AS CHAR) SEPARATOR ', ') AS rubric_title,
  profiles.user_id
FROM
  profiles,
  profiles_rubrics 
    JOIN rubrics ON profiles_rubrics.rubric_id=rubrics.id,
  users
WHERE
  profiles_rubrics.profile_id=profiles.id
  AND profiles_rubrics.rubric_id=rubrics.id
  AND users.id=profiles.user_id
  AND users.profile_online IS NOT NULL
  AND users.role_id!=1
GROUP BY
  profiles.id

I tried it with help from other answers here at stackoverflow but can't get to the point where it returns all profiles. I'm not a big MySQL expert as one might see from everything above :)

Answer 1

You need to make use of LEFT JOINS.

Something like

SELECT 
  profiles.id, 
  profiles.company, 
  GROUP_CONCAT(DISTINCT CAST(rubrics.id AS CHAR) SEPARATOR ', ') AS rubric, 
  GROUP_CONCAT(DISTINCT CAST(rubrics.title AS CHAR) SEPARATOR ', ') AS rubric_title, 
  profiles.user_id 
FROM 
  profiles LEFT JOIN
  profiles_rubrics  ON  profiles_rubrics.profile_id=profiles.id LEFT JOIN
  rubrics   ON  profiles_rubrics.rubric_id=rubrics.id LEFT JOIN
  users ON  users.id=profiles.user_id 
WHERE 
  users.profile_online IS NOT NULL 
  AND users.role_id!=1 
GROUP BY 
  profiles.id 

Have a look at SQL Joins