SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
Thread: Join 3 tables
-
Feb 3, 2006, 04:58 #1
- 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` (
`id` int(11) NOT NULL default '0',
`fisrtname` varchar(30) NOT NULL default '',
`lastname` varchar(30) NOT NULL default '',
`sex` enum('f','m') NOT NULL default 'k',
`groupid` int(11) NOT NULL default '0',
`roomid` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
)
CREATE TABLE `group` (
`id` int(11) NOT NULL auto_increment,
`groupname` varchar(30) NOT NULL default '',
`firstname` varchar(30) NOT NULL default '',
`lastname` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`)
)
CREATE TABLE `room` (
`id` nt(11) NOT NULL auto_increment,
`roomnumber` varchar(10) NOT NULL default '',
`type` enum('f','m') collate NOT NULL default 'k',
`maxnumberofbeds` int(11) NOT NULL default '0',
`leader` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
)
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
-Helge
-
Feb 3, 2006, 09:27 #2
- 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
-
Feb 3, 2006, 09:51 #3
- 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.
-
Feb 3, 2006, 10:11 #4
- 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
-
Feb 3, 2006, 10:45 #5
- Join Date
- Jan 2003
- Location
- Munich, Germany
- Posts
- 1,391
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
- 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:
Originally Posted by r937
We are the Borg. Resistance is futile. Prepare to be assimilated.
I'm Pentium of Borg.Division is futile.Prepare to be approximated.
-
Feb 8, 2006, 08:13 #6
- 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!
Originally Posted by r937
Originally Posted by frezno
Originally Posted by r937
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
-
Feb 8, 2006, 09:10 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Helge
Das muss aufhören! Das ist Quatsch!
Bookmarks