SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    May 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Join Problem

    Hi I have an SQL problem with this query:

    Code:
    SELECT cat_id, parent_id FROM categories Categories LEFT OUTER JOIN user_categories UserCategories ON Categories.cat_id = UserCategories.cat_id LEFT OUTER JOIN role_categories RoleCategories ON Categories.cat_id = RoleCategories.cat_id WHERE UserCategories.user_id = 11111 AND RoleCategories.role_id = 91919
    How do i get the results that are not on the UserCategories table but are on the RoleCategories table?

    Categories Table Data:
    cat_id = 1, parent_id = 0
    cat_id = 2, parent_id = 1
    cat_id = 3, parent_id = 2
    cat_id = 4, parent_id = 3
    cat_id = 5, parent_id = 4
    cat_id = 6, parent_id = 5

    RoleCategories Table Data:
    role_id = 91919, cat_id = 1
    role_id = 91919, cat_id = 2
    role_id = 91919, cat_id = 3
    role_id = 91919, cat_id = 4
    role_id = 91919, cat_id = 5

    UserCategories Table Data:
    user_id = 11111, cat_id = 1
    user_id = 11111, cat_id = 2
    user_id = 11111, cat_id = 3
    user_id = 11111, cat_id = 5

    Results i get with query above:
    cat_id = 1, parent_id = 0
    cat_id = 2, parent_id = 1
    cat_id = 3, parent_id = 2
    cat_id = 5, parent_id = 4

    Results i want:
    cat_id = 1, parent_id = 0
    cat_id = 2, parent_id = 1
    cat_id = 3, parent_id = 2
    cat_id = 4, parent_id = 3
    cat_id = 5, parent_id = 4

    SQL gods, please help me!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT 
        Categories.cat_id
      , Categories.parent_id 
    FROM categories Categories 
    LEFT OUTER JOIN user_categories UserCategories 
    ON Categories.cat_id = UserCategories.cat_id 
    LEFT OUTER JOIN role_categories RoleCategories 
    ON Categories.cat_id = RoleCategories.cat_id 
    WHERE UserCategories.user_id = 11111 
    AND RoleCategories.role_id = 91919
    I've formatted your query a bit, because 1-line-queries are impossible to read and understand.

    In your query you use left joins, and then put criteria on them in the WHERE clause, which makes them act like INNER joins. If you really want to have a LEFT join, put the where clauses in the respective ON clauses.
    And if you only want results where the join actually returns a row from the second table, use an INNER join.

    Code:
    SELECT 
        Categories.cat_id
      , Categories.parent_id 
    FROM categories Categories 
    INNER JOIN role_categories RoleCategories 
    ON Categories.cat_id = RoleCategories.cat_id 
    LEFT OUTER JOIN user_categories UserCategories 
    ON Categories.cat_id = UserCategories.cat_id 
    AND UserCategories.user_id = 11111 
    WHERE RoleCategories.role_id = 91919
    I don't understand what you are trying to accomplish though. If you don't care about the usercategories (you don't select a column from that table, and you don't care if the cat_id is present in that table) then why are you joining it?

  3. #3
    SitePoint Member
    Join Date
    May 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not really that i don't care about UserCategories. If the UserCategories Data would be:

    UserCategories Table Data:
    user_id = 11111, cat_id = 1
    user_id = 11111, cat_id = 2
    user_id = 11111, cat_id = 3
    user_id = 11111, cat_id = 5
    user_id = 11111, cat_id = 6

    And RoleCategories stay the same:

    Result should be:
    cat_id = 1, parent_id = 0
    cat_id = 2, parent_id = 1
    cat_id = 3, parent_id = 2
    cat_id = 4, parent_id = 3
    cat_id = 5, parent_id = 4
    cat_id = 6, parent_id = 5

    I'll try your query and see if it gets the desired results.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Ok, so you want all categories that are present in at least one of the other two tables.
    Then the query would become:
    Code:
    SELECT 
        Categories.cat_id
      , Categories.parent_id 
    FROM categories Categories 
    LEFT OUTER JOIN user_categories UserCategories 
    ON Categories.cat_id = UserCategories.cat_id 
    AND UserCategories.user_id = 11111 
    LEFT OUTER JOIN role_categories RoleCategories 
    ON Categories.cat_id = RoleCategories.cat_id 
    AND RoleCategories.role_id = 91919
    With your WHERE criteria in the respective ON clauses like I explained before.

  5. #5
    SitePoint Member
    Join Date
    May 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried the second query you sent and it returned a lot of undesired results. The first one worked for me though.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    The first one works for this particular situation, but not for the second situation you described.
    My second query isn't correct because it gives all rows in the categories table, and you want only those present in at least one of the two joined tables. I forgot to check that:
    Code:
    SELECT 
        Categories.cat_id
      , Categories.parent_id 
    FROM categories Categories 
    LEFT OUTER JOIN user_categories UserCategories 
    ON Categories.cat_id = UserCategories.cat_id 
    AND UserCategories.user_id = 11111 
    LEFT OUTER JOIN role_categories RoleCategories 
    ON Categories.cat_id = RoleCategories.cat_id 
    AND RoleCategories.role_id = 91919
    WHERE UserCategories.cat_id IS NOT NULL
    OR RoleCategories.cat_id IS NOT NULL
    See the WHERE clause? It checks if at least one of the joined tables returns a value that is not NULL, in other words if a row has been found.

  7. #7
    SitePoint Member
    Join Date
    May 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It still returned undesirable results but lesser now.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Which undesirable results?

  9. #9
    SitePoint Member
    Join Date
    May 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some of the role_id's are NULL and it seems like it got all that have the user_id.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Please give the exact data of the rows that are being selected and that are undesirable.
    Do a SELECT * to see the complete result of the joins


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
  •