3 tables join

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.

For eg.

TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}

the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be

id recordType column1

1 TableB value of TableB column1
2 TableC value of TableC column1

Please help.

Cheers
NickOn Nov 8, 3:29 pm, Nick <nachiket.shirwal...@.gmail.comwrote:

Quote:

Originally Posted by

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.
>
For eg.
>
TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}
>
the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be
>
id recordType column1
>
1 TableB value of TableB column1
2 TableC value of TableC column1
>
Please help.
>
Cheers
Nick

Hi Nick,

Try:
SELECT a.id, a.recordType, CASE WHEN b.column1 IS NULL THEN c.column1
ELSE b.column1 END AS column1
FROM TableA a
LEFT OUTER JOIN TableB b
ON b.id = a.relatedkey
AND a.recordType = 'TableB'
LEFT OUTER JOIN TableC c
ON c.id = a.relatedkey
AND a.recordType = 'TableC'

Good luck!
J|||On Thu, 08 Nov 2007 07:29:06 -0800, Nick wrote:

Quote:

Originally Posted by

>I have have 3 tables TableA, TableB and TableC. TableA holds the keys
>to TableB and TableC. I need a query which will display the details
>from TableB and TableC depending on the key in TableA.
>
>For eg.
>
>TableA - columns {id, relatedkey, recordType} ===recordType will hold
>values like TableB or TableC
>TableB - columns{id, column1}
>TableC - columns{id, column1}
>
>the query should match the related key to the id of TableA or table B
>based on recordType and show the column1 value with the TabelA id so
>output for this should be
>
>
>id recordType column1
>
>1 TableB value of TableB column1
>2 TableC value of TableC column1
>
>Please help.

Hi Nick,

The solution jhofmeyr posted will work for you. But I think you should
question your design. If TableB and TableC are actually the same thing,
they should be a single table. And if they are different things, then
TableA should have two referencing columns plus a CHECK constraint to
ensure that mey not both be NOT NULL.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis