SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left Join causing query to 'break'

    I'll start out with my broken query:
    Code MySQL:
    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.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by downtroden View Post
    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

    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  6. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    Rudy, I just LOVE the way you layout your queries! Just so easy to follow
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by downtroden View Post
    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

    Quote Originally Posted by downtroden View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •