Left Join causing query to 'break'

[downtroden]

I'll start out with my broken query:

SELECT  u.firstName, u.lastName, u.email, b.intro AS copy, b.createdAt,
		b.title, b.id AS blogId, b.categoryId, 'blog' AS type,
		 CAST(GROUP_CONCAT(DISTINCT r.roleid SEPARATOR ',') AS CHAR) AS view_roles
FROM blogs b
LEFT JOIN users u ON b.userid = u.id
LEFT JOIN roles r ON b.categoryid = r.categoryid AND r.type = 'view'
UNION
SELECT  u.firstName, u.lastName, u.email, 
		c.comment AS copy, c.createdAt,
		b.title, c.blogid, b.categoryId,
		'comment' AS type,
		 CAST(GROUP_CONCAT(DISTINCT r.roleid SEPARATOR ',') AS CHAR) AS view_roles
FROM comments c
LEFT JOIN users u ON c.userid = u.id 
LEFT JOIN blogs b ON c.blogid = b.id
LEFT JOIN roles r ON b.categoryid = r.categoryid AND r.type = 'view'
ORDER BY createdAt DESC
LIMIT 10
;

So I'm wanting to combine the blogs table and comments table into one, then limit to the 10 most recent which is fine, but it breaks down when I add the LEFT JOIN for the roles table (both queries). I'm needing the associated roles in a comma delimited list under the column 'view_roles'. When I don't include the join for the roles, I get an expected amount of rows returned. When it IS included it's limiting the rows to 2.

Schema is as follows:
Users:

Blogs:

Comments:

Roles:


I feel like either I'm making a simple mistake OR the query has gotten too complex.

Thanks for any assistance.

[r937]

without looking at your query in any great detail, i notice that you are using an aggregate function, GROUP_CONCAT, without a GROUP BY clause

so, yeah, it's a simple mistake

[downtroden]

Adding GROUP BY createdAt just doesn't seem correct. Still getting less than expected results.

The 'difficult' part of this query is I'm needing a column that is comma separated list of role id's That I can compare on the application server (Coldfusion) on whether to show or not.

Just had a realization that if I limit to 10 in the database query that a user might not see anything (if they're role list doesn't apply).

Here the problem presented to me. They want a 'facebook' like list of the most recent blog posts and comments for blog posts (with newest posts first). Blogs are inside categories which have view permissions. If the person can't see the category, they shouldn't see the blog post in the list.

I'm starting to think that I need to be involving CF in on creating this query.

I feel like this SHOULD be pretty easy, it's the whole converting columns to comma list that hangs me up.

[r937]

Adding GROUP BY createdAt just doesn't seem correct.

yeah, it isn't -- you want to GROUP BY the category, to get all the roles for the category

SELECT u.firstName
     , u.lastName
     , u.email
     , b.intro AS copy
     , b.createdAt
     , b.title
     , b.id AS blogId
     , b.categoryId
     , 'blog' AS type
     , r.view_roles
  FROM blogs b
INNER
  JOIN users u 
    ON u.id = b.userid
INNER
  JOIN ( SELECT categoryid
              , CAST(GROUP_CONCAT(roleid) AS CHAR) AS view_roles
           FROM roles 
          WHERE type = 'view'
         GROUP
             BY categoryid ) AS r 
    ON r.categoryid = b.categoryid 
UNION ALL
SELECT u.firstName
     , u.lastName
     , u.email
     , c.comment AS copy
     , c.createdAt
     , b.title
     , c.blogid
     , b.categoryId
     , 'comment' AS type
     , r.view_roles
  FROM comments c
INNER
  JOIN users u 
    ON u.id = c.userid
INNER
  JOIN blogs b 
    ON b.id = c.blogid
INNER
  JOIN ( SELECT categoryid
              , CAST(GROUP_CONCAT(roleid) AS CHAR) AS view_roles
           FROM roles 
          WHERE type = 'view'
         GROUP
             BY categoryid ) AS r 
    ON r.categoryid = b.categoryid 
ORDER 
    BY createdAt DESC LIMIT 10

note also it's UNION ALL

[downtroden]

okay, so it was the SELECT statement IN the INNER JOIN that was the answer…*thank you. Are sub-selects like this only valid in JOIN statements?

Do they have an official name?

And I can find out myself, but is there a difference between UNION and UNION ALL?

Thanks for the help.

[spikeZ]

Off Topic:

Rudy, I just LOVE the way you layout your queries! Just so easy to follow

[r937]

Are sub-selects like this only valid in JOIN statements?

in the FROM clause, subselects are often called derived tables or inline views (for reasons that are prettty evident in both cases)

subselects are also allowed in the SELECT clause, where they are called scalar subqueries, because here they are allowed to return only one row consisting of one column, i.e. a scalar value

And I can find out myself, but is there a difference between UNION and UNION ALL?

yes

UNION is actually the same as UNION DISTINCT (the DISTINCT keyword is the default) and indicates that the entire result set will be scanned for duplicate rows -- entire rows, all columns

UNION ALL skips this scan, and accepts all result rows, including duplicate rows, if any

in the case of your query, there won't be any dupes, because one subselect generates rows with 'blog' and the other generates rows with 'comment'

Off Topic:

thanks, mike... too bad the sitepoint coding style munged the sql code in my book... well, except for the leading comma convention, which, after some discussion, they begrudgingly accepted