February 10, 2010 at 3:43 am
Hi All,
I have a table-- TableA which could be linked to either TableB or TableC. I want to join either of the latter depending on which one is linked TableA. The two tables cannot be linked to TableA at the same time.
I want to do something like this:
SELECT *
FROM TableA TA
-- if TA.tbid is not null
LEFT OUTER JOIN TableB TB ON TB.tbid = TA.tbid
OR
-- if TA.tcid is not null
LEFT OUTER JOIN TableC TC ON TC.tcid = TA.tcid
February 10, 2010 at 4:21 am
phumlo1 (2/10/2010)
Hi All,I have a table-- TableA which could be linked to either TableB or TableC. I want to join either of the latter depending on which one is linked TableA. The two tables cannot be linked to TableA at the same time.
I want to do something like this:
SELECT *
FROM TableA TA
-- if TA.tbid is not null
LEFT OUTER JOIN TableB TB ON TB.tbid = TA.tbid
OR
-- if TA.tcid is not null
LEFT OUTER JOIN TableC TC ON TC.tcid = TA.tcid
Of course they can:
SELECT *
FROM TableA TA
LEFT OUTER JOIN TableB TB ON TB.tbid = TA.tbid
LEFT OUTER JOIN TableC TC ON TC.tcid = TA.tcid
If you have a business reason for not wishing to do this, then can you explain it in some detail?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 10, 2010 at 4:23 am
You'll probably get some remarks on the quality of this design, but I'll skip that and just try to answer your question.
You could do something like this
SELECT * -- you should put column names here, do not use *
FROM TableA TA
LEFT JOIN TableB TB
ON TB.tbid = TA.tbid
WHERE TA.tbid IS NOT NULL
UNION ALL
SELECT * -- you should put column names here, do not use *
FROM TableA TA
LEFT JOIN TableC TC
ON TC.tcid = TA.tcid
WHERE TA.tcid IS NOT NULL
February 10, 2010 at 4:33 am
DECLARE @tblA TABLE
(
IDINT
)
DECLARE @tblB TABLE
(
IDINT
)
DECLARE @tblC TABLE
(
IDINT
)
INSERT INTO @tblA
SELECT 1
UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
INSERT INTO @tblB
SELECT 1
UNION ALL
SELECT 2
INSERT INTO @tblC
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
SELECT A.ID,
CASE WHEN B.ID IS NULL THEN CASE WHEN C.ID IS NOT NULL THEN C.ID ELSE NULL END ELSE B.ID END,
CASE WHEN B.ID IS NULL THEN CASE WHEN C.ID IS NOT NULL THEN 'C' ELSE NULL END ELSE 'B' END AS [FROM TABLE]
FROM ( @tblA A LEFT JOIN @tblB B ON A.ID = B.ID) LEFT JOIN @tblC C ON A.ID=C.ID
Regards,
Mitesh OSwal
+918698619998
February 10, 2010 at 6:54 am
Thanks for all the replies.
Willem, your solution does give me the result I want. However, i wonder if there is a better way to do it since in my scenario there are 6 tables which are linked to TableA, and they are in turn all linked to another table(Table1), so I have to "Union All" the six tables and join Table1 for the one linked to TableA.
This means i have to write something almost similar 6 times.
Any suggestions welcome.
Regards
PH
February 10, 2010 at 7:03 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply