CODEWITHSUNDEEP
sqljoinforeign-keys
user1484093
user1484093

Reputation: 11

Join two tables containing two foreign keys

I'm pretty amateur when it comes to writing SQL, so maybe this is an easy one for someone? I have two tables as follows:

Table1 - DeviceName, DeviceID, AlternateID

Table2 - PhoneID, ProgramName

As you can see, Table2 uses two different PhoneID types from Table1 (DeviceID and AlternateID). I'm looking a sql statement that will result in output like:

Appreciate any assistance.

Cheers, Mark

Upvotes: 0

Views: 1785

Answers (2)

dbenham
dbenham

Reputation: 130819

What if both 333 and AAA are defined in Table 2? I'm going to assume you only want want one row returned per DeviceName, which means you need to join to Table 2 twice using outer joins:

select DeviceName, 
       t2a.ProgramName, 
       t2b.ProgramName as AltProgramName
  from table1 t1
  left outer join table2 t2a
    on t1.DeviceID = t2a.PhoneID
  left outer join table2 t2b
    on t1.AlternateID = t2b.PhoneID

If you want to list only one ProgramName, and can establish a priority as to which to use in case both are present, you could do the following: (assuming DeviceID trumps AlternateID)

select DeviceName, 
       coalesce(t2a.ProgramName, t2b.ProgramName) as AltProgramName
  from table1 t1
  left outer join table2 t2a
    on t1.DeviceID = t2a.PhoneID
  left outer join table2 t2b
    on t1.AlternateID = t2b.PhoneID

If you want the 1st program column to always contain a value, and only list a value in the 2nd column if both are present, then

select DeviceName, 
       coalesce(t2a.ProgramName, t2b.ProgramName) as ProgramName1,
       case when t2a.ProgramName is not null then t2b.ProgramName end as ProgramName2
  from table1 t1
  left outer join table2 t2a
    on t1.DeviceID = t2a.PhoneID
  left outer join table2 t2b
    on t1.AlternateID = t2b.PhoneID


EDIT

I assumed the query was being used for reporting purposes, in which case you most likely only want one row per device.

I just realized why the OP might want multiple rows returned per device. If the query is used to define a view for looking up devices by either name, then multiple rows are desired.

Jan's answer works perfectly for the lookup view. However, it may or may not perform well depending on the database engine's query optimizer and the size of the tables. If the tables are very large, then you are going to want an indexed lookup. The join with an OR condition may preclude an indexed lookup on some systems. An equivalent query using UNION ALL may support an indexed lookup on more systems.

select DeviceName,
       ProgramName
  from table1 t1
  join table2 t2
    on t1.DeviceID = t2.PhoneID
union all
select DeviceName,
       ProgramName
  from table1 t1
  join table2 t2
    on t1.AlternateID = t2.PhoneID

Upvotes: 0

John Dvorak
John Dvorak

Reputation: 27277

SELECT deviceName, programName
FROM table2 t2
JOIN table1 t1
 ON(t1.DeviceID=t2.PhoneID OR t1.AlternateID=t2.PhoneID)

or (less readable but shorter)

SELECT deviceName, programName
FROM table2 t2
JOIN table1 t1
 ON(t2.PhoneID IN (t1.DeviceID, t1.AlternateID))

Still, if DeviceIDs and AlternateIDs are from the same set, you should consider refactoring the database: What if a device could have multiple valid IDs, not just two?

Upvotes: 4

Related Questions