One-to-Many Table Join?

Question

I have one table (webRooms) which has a number of rows which match those in a second table (Residency). Table 1 looks like this:

ID  |  dorm_building  |  dorm_room |  occupant_num

Table 2 looks like this:

student_ID  |  dorm_building  | dorm_room

What I'd like is to get results like this:

    ID  | dorm_building  | dorm_room  | occupant_num  | student_id
    1   |  my_dorm       | 1          | 1             | 123
    2   | my_dorm        | 1          | 2             | 345

But what I get currently looks like this:

    ID  | dorm_building  | dorm_room  | occupant_num  | student_id
    1   |  my_dorm       | 1          | 1             | 123
    2   | my_dorm        | 1          | 2             | 123

I'm currently using a left join, any suggestions?

Current query looks like this:

select * from webRooms wR 
  LEFT JOIN RESIDENCY R on wR.dorm_building = r.DORM_BUILDING 
    and wr.dorm_room = r.DORM_ROOM 

Due to some of the answers given I'm adding a third table into the mix. This table has existed - it is what I use to generate the webRooms table, it is called webDorms and looks like this:

ID | dorm_building | dorm_room | max_occupancy

It has results like this:

2 | my_dorm | 1 | 2

Answer 1

I think your data model is flawed. Currently your model has multiple records per Room, one per Slot. Because your query only restrict Students to Rooms not Slots it produces a cross-join, which is the wrong result.

It is possible to kludge your query to overcome the shortcomings of the model. The DISTINCT keyword is the blunt instrument of choice in these scenarios:

SQL> select *
  2      from ( select DISTINCT dorm_building, dorm_room from webRooms) wR
  3          LEFT JOIN residency R
  4          on wR.dorm_building = r.dorm_building
  5          and wr.dorm_room = r.dorm_room
  6  /

DORM_BUILDING         DORM_ROOM STUDENT_ID DORM_BUILDING         DORM_ROOM
-------------------- ---------- ---------- -------------------- ----------
my_dorm                       1        123 my_dorm                       1
my_dorm                       1        345 my_dorm                       1
my_dorm                       2

SQL>

---

A better way to tackle it would be with a SLOTS table. This removes the need to have multiple WEBROOMS records to represent a single physical Room. You say it is "inconsequential" which Slot a Student is assigned to, but it is key to the successful working of the application that a Student is assigned to a specific Slot.

Here are some proof of concept tables:

create table webrooms
 (dorm_building varchar2(20)
    , dorm_room number)
/

create table slots
 (dorm_building varchar2(20)
    , dorm_room number
    , occupant_num number)
/

create table residency
 (student_id number
    , dorm_building varchar2(20)
    , dorm_room number
    , occupant_num number)
/

As you can see, the revised query provides clear indications of which Slots are occupied and which remains free:

SQL> select wr.*, s.occupant_num, r.student_id
  2      from webrooms wr
  3          INNER JOIN slots s
  4              on wr.dorm_building = s.dorm_building
  5              and wr.dorm_room = s.dorm_room
  6          LEFT JOIN residency r
  7              on s.dorm_building = r.dorm_building
  8              and s.dorm_room = r.dorm_room
  9              and s.occupant_num = r.occupant_num
 10  order by 1, 2, 3, 4
 11  /

DORM_BUILDING         DORM_ROOM OCCUPANT_NUM STUDENT_ID
-------------------- ---------- ------------ ----------
my_dorm                       1            1        123
my_dorm                       1            2        345
my_dorm                       2            1        678
my_dorm                       2            2
my_dorm                       2            3        890
my_dorm                       3            1
my_dorm                       3            2
my_dorm                       3            3
my_dorm                       4            1
my_dorm                       4            2        666

9 rows selected.

SQL>

Or, if we have a database which supports PIVOT queries (I'm using Oracle 11g here):

SQL> select * from (
  2      select wr.dorm_building||' #'||wr.dorm_room as dorm_room
  3             , num_gen.num as slot_number
  4             , case
  5                  when r.student_id is not null then r.student_id
  6                  when s.occupant_num is not null then 0
  7                  else null
  8               end as occupancy
  9          from webrooms wr
 10              CROSS JOIN ( select rownum as num from dual connect by level <= 4) num_gen
 11              LEFT JOIN slots s
 12                  on wr.dorm_building = s.dorm_building
 13                  and wr.dorm_room = s.dorm_room
 14                  and num_gen.num = s.occupant_num
 15              LEFT JOIN residency r
 16                  on s.dorm_building = r.dorm_building
 17                  and s.dorm_room = r.dorm_room
 18                  and s.occupant_num = r.occupant_num
 19      )
 20  pivot
 21      ( sum (occupancy)
 22        for slot_number in ( 1, 2, 3, 4)
 23      )
 24  order by dorm_room
 25  /

DORM_ROOM           1          2          3          4
---------- ---------- ---------- ---------- ----------
my_dorm #1        123        345
my_dorm #2        678          0        890
my_dorm #3          0          0          0
my_dorm #4          0        666

SQL>

Answer 2

You mentioned in comments to APC's post that all you want is counts of availability. If that is actually the case, then I would think the following would be a more efficient design:

Create Table Rooms  (
                        dorm_building ... Not Null
                        , dorm_room ... Not Null
                        , capacity int Not Null default ( 0 )
                        , Constraint PK_Rooms Primary Key ( dorm_building, dorm_room )
                        , ...
                        )

Create Table Residency  (
                            student_id ... Not Null Primary Key
                            , dorm_building ... Not Null
                            , dorm_room ... Not Null
                            , Constraint FK_Residency_Rooms
                                Foreign Key ( dorm_building, dorm_room )
                                References Rooms ( dorm_building, dorm_room )
                            , ...
                            )

I made student_id the primary key in the Residency table only because there is no mention of a time element and it shouldn't be possible for a student to be in two rooms at the same time. Now, to get the available space we can do:

Select Rooms.dorm_building, Rooms.dorm_room
    , Rooms.Capacity
    , Coalesce(RoomCounts.OccupantTotal,0) As TotalOccupants
    , Rooms.Capacity - Coalesce(RoomCounts.OccupantTotal,0) As AvailableSpace
From Rooms
    Left Join   (
                Select R1.dorm_building, R1.dorm_room, Count(*) As OccupantTotal
                From Residency As R1
                Group By R1.dorm_building, R1.dorm_room
                ) As RoomCounts
        On RoomCounts.dorm_building = Rooms.dorm_building
            And RoomCounts.dorm_room = Rooms.dorm_room

Now, if you also want to display "slots", then you should calculate that on the fly (this assumes SQL Server 2005 and later):

With Numbers As
    (
    Select Row_Number() Over ( Order By C1.object_id ) As Value
    From sys.columns As C1
        Cross Join sys.columns As C2
    )
    , NumberedResidency As
    (
    Select dorm_building, dorm_room, student_id
        , Row_Number() Over ( Partition By dorm_building, dorm_room Order By student_id ) As OccupantNum
    From Residency
    )
Select Rooms.dorm_building, Rooms.dorm_room, R.OccupantNum, R.StudentId
From Rooms
    Join Numbers As N
        On N.Value <= Rooms.Capacity
    Left Join NumberedResidency As R
        On R.dorm_building = Rooms.dorm_building
            And R.dorm_room = Rooms.dorm_room
            And N.Value = R.OccupantNum