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