November 10, 2005 at 9:49 am
How/Can you fall thru to a default join if an inner fails? I thought of creating temp table with all values, then updating, but wonder if I can accomplish in one step.
Basically master tblB has 'H' types for ALL codes. I want to join tblA on code /type, but if type doesn't exist in tbleB, default to type 'H'.
tblA -
code type
111 B
111 C
222 B
333 A
444 D
tblB -
code type
111 H
111 C
222 H
333 A
333 H
444 H
555 H
Need all of tblA joined to tblB on code and type.
If tblB does NOT have type, default (join) to type 'H'
tblA codes 111 type B, 222 type B, 444 type D should default to tblB type H, all others have matches.
November 10, 2005 at 10:13 am
This should give you what you're looking for. I wasn't able to test it so beware of Typo's
SELECT A.code
, CASE WHEN B.type IS NULL
THEN H.type
ELSE B.type
END As type
FROM tblA AS A
LEFT JOIN tblB AS B
ON A.code = B.code
AND A.Type = B.Type
LEFT JOIN tblB AS H
ON A.code = H.code
AND H.type = 'H'
Mark
November 10, 2005 at 10:20 am
I think it may be simpler:
SELECT A.code
, ISNULL( B.Type, 'H') as type
FROM tblA AS A
LEFT JOIN tblB AS B
ON A.code = B.code
AND A.Type = B.Type
* Noel
November 10, 2005 at 10:26 am
Noel,
I was just trying to make sure that if there was no corresponding 'H' in the table, that it wouldn't return one.
Mark
November 10, 2005 at 10:27 am
Thanks, I was going down that 2nd alias road when I realized that I also had to join on tblB. Now it's gets a bit confusing. That tblB has to join to tblC, which tblC itself joins to tblD.
So I do need the tblB record, not just default the value.
November 10, 2005 at 10:30 am
Mark is correct if your default value is not there!!!
I just trusted the poster affirmation: Basically master tblB has 'H' types for ALL codes
If that is not the case you must use the two left joins posted by Mark
* Noel
November 10, 2005 at 10:30 am
Actually there is always a type = 'H' (default) in table 'B' as long as the code exists. The problem is if there is a match on another type, use that, if not, grab the 'H' record. Then I need to join to that (tblB) to aquire other data.
November 14, 2005 at 5:24 am
OK, it seems that it is more complicated :-). Right now I can see two possible ways to do that, but I can't gurantee that any of them will work as required.
1) Prepare the value on which to join in the first step, using a view or derived table, then join only once. This is more likely to work correctly, but I can't guess at what performance. Example shows use of derived table with a "calculated" column new_type.
SELECT Q.code, Q.new_type, tblC.some_col, tblD.some_col
FROM
(SELECT tblA.code, tblA.type,
CASE WHEN EXISTS (select * from tblB where code = tblA.code and type = tblA.type)
THEN tblA.type ELSE 'H' END as new_type
FROM tblA) AS Q
LEFT JOIN tblB ON tblB.code = Q.code AND tblB.type = Q.new_type
LEFT JOIN tblC ON tblC.id_col = tblB.id_col
LEFT JOIN tblD ON tblD.id_col = tblC.id_col
2) Join the table tblB twice (once on the actual type, once on H). The second branch may work with full joins, but I don't know anything about these tables, so I used LEFT everywhere. This method could produce duplication of rows, depending on the data; also, if some of the values selected from tables C or D can be NULL, you would get wrong result (part of info will be from the rows joined through the actual type and part from the H branch) - so be careful. Use of this approach is very limited (I mentioned it because I have used something very similar with success, thanks to the structure of data).
SELECT tblA.code, ISNULL(tblB1.type,tblB2.type), ISNULL(tblC1.some_col, tblC2.some_col), ISNULL(tblD1.some_col, tblD2.some_col)
FROM tblA
LEFT JOIN tblB tblB1 ON tblB1.code = tblA.code AND tblB1.type = tblA.type
LEFT JOIN tblC tblC1 ON tblC1.id_col = tblB1.id_col
LEFT JOIN tblD tblD1 ON tblD1.id_col = tblC1.id_col
LEFT JOIN tblB tblB2 ON tblB2.code = tblA.code AND tblB2.type = 'H'
LEFT JOIN tblC tblC2 ON tblC2.id_col = tblB2.id_col
LEFT JOIN tblD tblD2 ON tblD2.id_col = tblC2.id_col
HTH, Vladan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply