September 9, 2010 at 2:04 pm
I have a main table. To that I would like to join another several times. It makes a (sort of) cross tab. It works great until (just) one of the joins return no records. When that happens NOTHING is returned.
First join
Right Join
(
Select
tbl_WD_F.D_Class_ID As D_Class_ID
, Case
When tbl_WD_F.F_Type = 'BW' then tbl_WD_F.F_Value
Else 0
End
As F_Value_BW
From tbl_WD_F
Where tbl_WD_F.F_Type = 'BW'
) As vtbl_WD_F_BW
On vtbl_WD_F_BW.D_Class_ID = tbl_WD_Class.D_Class_ID
Yeah it works. Probably because on every join there is a matching F_Type.
So I add another join.
Right Join
(
Select
tbl_WD_F.D_Class_ID As D_Class_ID
, Case
When tbl_WD_F.F_Type = 'BW' then tbl_WD_F.F_Value
Else 0
End
As F_Value_BW
From tbl_WD_F
Where tbl_WD_F.F_Type = 'BW'
) As vtbl_WD_F_BW
On vtbl_WD_F_BW.D_Class_ID = tbl_WD_Class.D_Class_ID
Right Join
(
Select
tbl_WD_F.D_Class_ID As D_Class_ID
, Case
When tbl_WD_F.F_Type = 'D' then tbl_WD_F.F_Value
Else 0
End
As F_Value_D
From tbl_WD_F
Where tbl_WD_F.F_Type = 'D'
) As vtbl_WD_F_D
On vtbl_WD_F_D.D_Class_ID = tbl_WD_Class.D_Class_ID
This works by the way. But only if join returns a record. If any of the joins (2nd or 3rd or 4th or etc) returns no records then Zippo, nadda, nothing. Nothing is returned at all. I need to repeat this 8 times in total. I've tried this same code sequence: one at a time which works; joining just two which works if data exists for both joins. If there is a corosponding F_Type it works and I can display the records. If just one of the joins returns no records then no records are returned at all.
I have tried the IsNull statement (IsNull(field,'substitute')) for both fields but that didn't work either.
Let me know if I'm not explaining this well. I'm still new, or atleast it feels that way.
I would appreciate any help you could give me.
Thank you,
September 9, 2010 at 2:19 pm
Using right joins your last table is the one that has to have records, then the second to last to expand it, and so on.
If you're looking for results, no matter if there's a connection or not, start testing with using 'full outer join'.
This will produce the expected join results, and combinations on both sides that don't.
Otherwise I think we'll have to see the full query, and some more details on which tables (or derived tables from the subqueries) are returning 0 results, to determine what's causing the issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 9, 2010 at 2:29 pm
I can't believe I just made that simple of a mistake.
Thanks, just proves that when you look at something long enough you can't even see your own typos.
Sorry for the post but thanks for the help,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply