Join issue

Databases
1

I have 3 table t1, t2 and t3. t1 has 3 columns: id, recordid and tableid. t2and t3 have 2 columns id and name. I want to bring back all records from t2 and t3 where they have the same id as recordid in t1 and they are the correct table from t1.tableid:


SELECT t1.ID AS t1id, t1.recordid, t1.tableid, CASE WHEN t1.tableid = 2           THEN  t2.name ELSE t3.name END AS 'name'
FROM    t1 LEFT OUTER JOIN
           t3 ON t1.recordid = t3.id AND t1.typeid = 3 LEFT OUTER JOIN
           t2 ON t1.recordid = t2.id AND t1.typeid = 2

The problem is that if there is a record in t1 which does not link to t1 or t2 it is still returned with NULL as the name. I have tried using right outer joins instead, but then all the t1 info is null!

What do i need to do?

Cheers

monkey

2 

select t1.ID AS t1id, 
       t1.recordid, 
       t1.tableid, 
       t2.name 
  from t1 join t2
    on t1.recordid = t2.id 
 where t1.typeid = 2
 union all
select t1.ID AS t1id, 
       t1.recordid, 
       t1.tableid, 
       t3.name 
  from t1 join t3
    on t1.recordid = t3.id 
 where t1.typeid = 3

What do i need to do?

Ideally you should redesign your data model and consolidate t2 and t3 into one table with a type column.

3

Ideally you should redesign your data model and consolidate t2 and t3 into one table with a type column.

Funnily enough that was exactly what i was trying to avoid! I have come to the same conclusion but was trying to get round not doing it - oh well, here we go! :rolleyes: