SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Join 3 tables

Hybrid View

  1. #1
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join 3 tables

    Hi

    I'm having trouble getting data from three different tables. I have the following sturcture:
    PHP Code:
    CREATE TABLE `attendee` (
      `
    idint(11NOT NULL default '0',
      `
    fisrtnamevarchar(30NOT NULL default '',
      `
    lastnamevarchar(30NOT NULL default '',
      `
    sexenum('f','m'NOT NULL default 'k',
      `
    groupidint(11NOT NULL default '0',
      `
    roomidint(11NOT NULL default '0',
      
    PRIMARY KEY  (`id`)


    CREATE TABLE `group` (
      `
    idint(11NOT NULL auto_increment,
      `
    groupnamevarchar(30NOT NULL default '',
      `
    firstnamevarchar(30NOT NULL default '',
      `
    lastnamevarchar(30NOT NULL default '',
      
    PRIMARY KEY  (`id`)
    )  

    CREATE TABLE `room` (
      `
    idnt(11NOT NULL auto_increment,
      `
    roomnumbervarchar(10NOT NULL default '',
      `
    typeenum('f','m'collate NOT NULL default 'k',
      `
    maxnumberofbedsint(11NOT NULL default '0',
      `
    leaderint(11NOT NULL default '0',
      
    PRIMARY KEY  (`id`)

    I'm having the following query, but it returns an empty result set. I realise that joining table isn't my stength.
    PHP Code:
    SELECT attendee.firstname
         
    attendee.lastname
         
    attendee.sex
         
    group.firstname AS gfirstname
         
    group.lastname AS glastname
         
    room.nr AS room
      FROM attendee 
         
    room
         
    group
     WHERE attendee
    .groupid group.id
       
    AND attendee.roomid room.id
    ORDER BY attendee
    .lastname ASC 
    Any help is appreciated.

    -Helge

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, please rename the `group` table, because GROUP is a reserved word, and you'll forever have to backtick it

    other than that, your query looks okay, so if it's not returning any rows, it must be a data problem

    here's the way to write it with JOIN syntax

    nice formatting, by the way

    Code:
    SELECT attendee.firstname 
         , attendee.lastname 
         , attendee.sex 
         , `group`.firstname AS gfirstname 
         , `group`.lastname AS glastname 
         , room.nr AS room 
      FROM attendee 
    inner
      join room 
        on room.id = attendee.roomid 
    inner
      join `group`
        on `group`.id = attendee.groupid 
    ORDER 
        BY attendee.lastname ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i don't quite understand the group table
    why is there first- and lastname?
    if every attendee has to be assigned to a group, imho there's no need for it.

    i'm no good in JOINs by any means, so i try it 'old school:
    Code:
    SELECT a.firstname
    , a.lastname
    , a.sex
    , g.groupname
    , r.roomnumber
    FROM attendee a
    , `group` g
    , `room` r
    WHERE a.groupid = g.id
    	 AND a.roomid = r.id
    ORDER
    	 BY a.lastname ASC
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the way i interpreted the `group` table was for groups checking into a spa who would sleep in a common bedroom, so they have to all be of the same gender so as to avoid shenanigans

    each group is identified by a leader, whose firstname/lastname is in the `group` table (mrs. johnson's group, mr. pringle's group)

    a person can belong to several groups but only through a link from the group to a room

    and the room has a leader too!!! which probably links back to the `group` table

    frankly, this is why i try not to question table designs very often, because there is so much that could be going on, you'd spend all morning just chasing down design flaws (if any -- i'm not saying there are any, helge, just that sometimes they do show up if you start to examine the relationships)

    in any case i think it's still a data problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    each group is identified by a leader, whose firstname/lastname is in the `group` table (mrs. johnson's group, mr. pringle's group)
    right that's a possibility, but even then:
    - either the leader is one of the attendees he could be referred by an id
    - the leader does not belong to the attendees, then it might make sense to put leaders into a seperate table and then he/she can be referred by an id again.

    BUT:
    Quote Originally Posted by r937
    frankly, this is why i try not to question table designs very often, because there is so much that could be going on
    some words of wisdom (or is it experience)
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  6. #6
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for my late response, but I have been really busy over the weekend. We had a youth conference this weekend that i used this small application for. I managed to get the work done without the query above, but I've already startet to develope the application further, so I would love to solve the problem above. Thanks for any help so far!
    Quote Originally Posted by r937
    first of all, please rename the `group` table, because GROUP is a reserved word, and you'll forever have to backtick it
    Actually I have translated the SQLs to english when posting here. The `group` table is called `gruppe` in the application, so I guess that should be ok.
    Quote Originally Posted by frezno
    i don't quite understand the group table
    I'll try to explain the application. As said it's for a youth conference. Most of, but not all, of the attendees belongs to a group. This group is identified by the group leaders names. The group leaders may or may not be attending the conference. The room leaders should really be linked to the attendees table. The reason I didn't was because it was faster to write the names manually into the table than to develope the application. We only used 20 rooms. But this is one of the things I'll fix to next years conference.
    Quote Originally Posted by r937
    nice formatting, by the way
    I learned it from you.

    I'll try the the SQLs suggested and see if that work. Then I'll change to the correct linking between room leader and attendees table.

    I'll post back.

    -Helge

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Helge
    The `group` table is called `gruppe` in the application, so I guess that should be ok.
    yes, that's fine

    Das muss aufhören! Das ist Quatsch!
    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
  •