Join 3 tables

[Helge]

Hi

I'm having trouble getting data from three different tables. I have the following sturcture:

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`)
) 

I'm having the following query, but it returns an empty result set. I realise that joining table isn't my stength.

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

[r937]

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

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

[frezno]

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:

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

[r937]

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

[frezno]

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:

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)

[Helge]

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!

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.

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.

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

[r937]

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!