SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left join question

    EDIT: ok a new problem has now arisen, please my posts below for information, thanks for looking


    Ok, i have successfully managed to preform a left join on two of my tables, then i relised that i needed a third table, and it's all kinda gone out of the window from there

    setting the scene

    tables:

    categories
    id - pk
    name

    tutorials
    id - pk
    name

    tutorial_lookup
    cid
    tid
    (primary key = both)

    (tables have been simplifyed btw, but thats all you should need to know)

    what i know want to do is get a list of categories, with the number of tutorials in them, and also list tutorials under them

    so far i have managed the first two steps (listing categories with num of tutorials)

    i have used

    SELECT categories.name, categories.id AS catid,
    COUNT(tid) AS numtut
    FROM categories LEFT JOIN tutorial_lookup
    ON cid = categories.id
    GROUP BY categories.id
    ;

    now i need to add the tutorials table to this and retrieve the id and name fields.

    I have tried a few things, but the problem is, without knowing which option to use, i dont even now if i was close

    i have tried adding another left join and just another table seperated with a coma, but neither seemed to work


    Hopefully somebody wise can helpme

    Thanks for looking/contributing
    Last edited by bloo_fish; May 29, 2004 at 00:43.

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    ÷rebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    I don't know if I've understand what you want to accomplish but I'll give it a go.

    Code:
    SELECT tC.name, tC.id AS catid, tT.name AS tutName, tT.id AS tutId, COUNT(tL.tid) AS numtut
    FROM categories tC
      LEFT JOIN tutorial_lookup tL ON cid = categories.id
      LEFT JOIN tutorials tT ON tutorials.id = tL.tid
    GROUP BY categories.id
    Yours, Erik.

  3. #3
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lilleman
    Hi

    I don't know if I've understand what you want to accomplish but I'll give it a go.

    Code:
    SELECT tC.name, tC.id AS catid, tT.name AS tutName, tT.id AS tutId, COUNT(tL.tid) AS numtut
    FROM categories tC
      LEFT JOIN tutorial_lookup tL ON cid = categories.id
      LEFT JOIN tutorials tT ON tutorials.id = tL.tid
    GROUP BY categories.id
    Yours, Erik.
    Cheers M8, that works almost perfectly, i had to make a few little changes, i changed name of tut_look up to t.TL and also changed one of the full names down to an t.* to make it work

    Thanks for the help, and i never knew about the t.* naming thing before, really has sorted out my code now

    Well thanks again, and the sql that worked was


    Code:
    SELECT tC.name, tC.id AS catid, tT.title, tT.id AS tutid, 
    COUNT(tTL.tid) AS numtut
    FROM categories tC
    LEFT JOIN tutorial_lookup tTL ON tTL.cid = tC.id
    LEFT JOIN tutorials tT ON tT.id = tTL.tid
    GROUP BY tC.id

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    unpredicable results await those whose
    SELECT list contains non-aggregate columns
    not in the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    unpredicable results await those whose
    SELECT list contains non-aggregate columns
    not in the GROUP BY
    Hey dude, i'm still learning this stuff really, so could you care to expand on that a little, maybe any articles ect... i could read up on it?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    see 13.9.3 GROUP BY with Hidden Fields --
    MySQL extends the use of GROUP BY so that you can use columns or
    calculations in the SELECT list that don't appear in the
    GROUP BY clause. This stands for any possible value for
    this group
    ... Do not use this feature if the
    columns you omit from the GROUP BY part are not unique in
    the group! You will get unpredictable results.
    emphasis by mysql, not me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    see 13.9.3 GROUP BY with Hidden Fields --emphasis by mysql, not me
    Ok well thansk for that, its defentaly somthing i will be looking into, i seem to have hit a small problem with this query thou, it seems to be only showing me one result per category, and i need it to show all results per category. I have been playing around with this like constantly, since you guys helped me, but i still cant figure it out

    any ideas would be much appreciated

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "get a list of categories, with the number of tutorials in them, ..."
    Code:
    select categories.name     as catname
         , categories.id       as catid
         , count(tid)          as numtut
      from categories 
    left outer
      join tutorial_lookup
        on categories.id
         = tutorial_lookup.cid
    group 
        by categories.name
         , categories.id
    "... and also list tutorials under them"
    Code:
    select categories.name     as catname
         , categories.id       as catid
         , tutorials.name      as tutname
         , tutorials.id        as tutid
      from categories 
    left outer
      join tutorial_lookup
        on categories.id
         = tutorial_lookup.cid
    left outer
      join tutorials
        on tutorial_lookup.tid     
         = tutorials.id
    now, it appears as though you wanted to combine these results

    that's possible, with UNION
    Code:
    select categories.name     as catname
         , categories.id       as catid
         , 1                   as sortseq
         , count(tid)          as numtut
         , cast(null as varchar) as tutname 
         , cast(null as integer) as tutid  
      from categories 
    left outer
      join tutorial_lookup
        on categories.id
         = tutorial_lookup.cid
    group 
        by categories.name
         , categories.id 
    union all
    select categories.name     
         , categories.id      
         , 2               
         , cast(null as tinyint)
         , tutorials.name    
         , tutorials.id     
      from categories 
    left outer
      join tutorial_lookup
        on categories.id
         = tutorial_lookup.cid
    left outer
      join tutorials
        on tutorial_lookup.tid     
         = tutorials.id 
    order
        by catname
         , catid
         , sortseq
         , tutname 
         , tutid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey r937 - Firstly, Thanks a lot for the reply, looks like it would have taken a bit of your time to come up with that, I am very grateful, but the UNION query doesnít seem to work (MySQL command prompt).

    The error I receive is



    and the second problem is i am terribly confused by your MySQL query statement, so i am unable to fix this error, please could you try and explain it to me, then i might be able to learn how to re-create similar queries, and broaden my knowledge

    Thanks again for the help, itís very much appreciated

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    CAST was added in mysql 4.0.2.

    UNION is implemented in mysql 4.0.0.

    both are explained quite adequately in the online docs

    the two individual queries i gave you should be easy enough
    to understand -- one counts the tutorials in each category,
    the other lists them

    the UNION query combines the two results by ensuring that
    the result sets are union-compatible, which means the same
    number of columns, same datatype (which is why CAST is used,
    to ensure datatype compatibility)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, cheers m8, i'm downloading 4.1 now (couldn't see 4.02 for win, still only had 4.0.18 which is what i'm running)

    and now i'm of to read some online docs about that query

    Thanks again for all the help


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
  •