SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    London
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    get workers for GroupA and GroupB in one query (was "better query than i have")

    Two tables

    workers - pid, name
    groups - pid, groupA, groupB

    To get the names of groupA and groupB members, I've done queried twice - once for groupA and once for groupB

    For GroupA
    Code:
     SELECT name AS 'Group A' FROM workers LEFT JOIN groups ON workers.id = groups.id WHERE groupA='yes';
    For GroupB
    Code:
     SELECT name AS 'Group B' FROM workers LEFT JOIN groups ON workers.id = groups.id WHERE groupB='yes';
    I cannot think of a query which will get for GroupA and GroupB in one go.

    BTW - on another topic - can you LEFT JOIN twice ?....I mean have two tables left joining a third one on the same column...

    Marmot

  2. #2
    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)
    yes, you can LEFT OUTER JOIN to multiple tables joining on the same column

    but are you sure want a LEFT OUTER JOIN? and do you have the tables in the correct order?

    LEFT OUTER JOIN is used to return all rows of the left table, with matching rows, if any, from the right table

    your queries suggest you want workers with or without their groups

    however, your statement is "get the names of groupA and groupB members"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    London
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, you can LEFT OUTER JOIN to multiple tables joining on the same column

    but are you sure want a LEFT OUTER JOIN? and do you have the tables in the correct order?

    LEFT OUTER JOIN is used to return all rows of the left table, with matching rows, if any, from the right table

    your queries suggest you want workers with or without their groups

    however, your statement is "get the names of groupA and groupB members"
    thanks r937

    regards joins on multiple tables, I just wanted to know if it could be done: I'll explore some more now

    SELECT name, groupA, groupB FROM workers JOIN groups WHERE workers.id = groups.id

    this gets all the info needed: it's OK but just a bit of fiddle in php to create a two column html table populated by the names of workers in groupA and groupB.

    my two query solution can be echoed out in php with two single column tables stuck inside a two column table

    all a bit obtuse,i know but hey that's small furry Alpine rodents for you

    Marmot


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
  •