SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT syntax help please

    hello ,
    i done have any idea how does this works, i want to list all fathers and kids,
    (also who has no kids) by expamle like this:
    my first table1:

    id father
    1 | james
    2 | mark
    3 | jake

    and the last table2:
    id | kids | father_id
    1 | marry | 1
    2 | sara | 1
    3 | jarry | 2

    Okay now i used the:
    select father , count(kids) as 'total' from table1, table2 where table1.id=table2.father_id group by father order by total desc;
    okay this is will list this :
    james 2 kids
    mark 1 kids
    but the jake is missing!!!!
    i would like to do like this
    james 2 kids
    mark 1 kids
    jake 0 kids
    i know jake has no kids but i would like to list all father (also who has no kids yet)
    please any help ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select father 
         , count(kids) as total 
      from table1
    left outer
      join table2 
        on table1.id = table2.father_id 
    group 
        by father 
    order 
        by total desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you i think i still dont understand left outer , or right join
    where can i find some tutrials about them ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks pal


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
  •