SQLTeam.com | Weblogs | Forums

Joining tables to show one extra column


#1

Hi I have one existing query , I got new request today to add one more field to that query

Filed name is "DRSDecription" in Table DRSCodes

These are existing joins in query

left outer join DRSOrders oo on av.VisitID = oo.VisitID
left outer join DRSVisitEvents ave on av.VisitID = ave.VisitID

But DRS codes don't have any common filed with above joined tables...

But DRSCodesData table has VisitID in it and then we can join this with DRSCodes table with CodeID as filed

I added joins like below

left outer join DRSOrders oo on av.VisitID = oo.VisitID
left outer join DRSVisitEvents ave on av.VisitID = ave.VisitID
left outer join DRSCodesData dcd on av.VisitID = dcd.VisitID
left outer join DRSCodes dc on dcd.CodeID = dc.CodeID

Then pulling dc.DRSDecription as DRS Description... But given time frame it should pop up 262 records but after I added .this join "left outer join DRSCodes dc on dcd.CodeID = dc.CodeID" getting 2891 records.. which means pulling all unnecessary results..

How can I eliminate this.. Please suggest.. I need to submit this report ASAP.. I am new to this SQL..

Thank you all..


#2

why left join? Maybe you want an inner join?


#3

OK Thank you...

But I added Inner Join getting some issues that if any account has no description then that account is not coming in results...

If it don't have description that should show up "NuLL" description...

please help me


#4

That is the only issue right now.. Its pulling only the records which have CodeID...

Few of accounts don't have CodeID entered but still we need to show them.. how can I do that..

Please plase help me on this ASAP....

Thanks,
Archana


#5

Since I don't know your data, I can only offer some hints:

when you see an explosion of results after adding a new join, often you don't have enough conditions in the join predicate. e.g. you write:

select foo
from bar
join fum on bar.baz = fum.baz

but need

select foo
from bar
join fum on bar.baz = fum.baz and bar.foe = fum.foe

The other possibility is that the new table is a many to 1. so you need something like

select foo
from bar
join (select distinct baz from bar) bar
on bar.baz = foo.baz

hope that helps.


#6

The Problem is

we looking for DRSCodesData.CodeID = DRSCodes .CodeID ...

For few accounts DRSCodesData.CodeID = NULL entered .. But DRSCodes Table don't have NULL values .. because of that it eliminating Null values...

DRSCodes Table has description only when there is CodeID exist..

Do we need to add any external code to include these NULL values ?

Please help me..

Do we need


#7

Hello anyone there

Please help me on this...

I am using below Joins

left outer join DRSOrders oo on av.VisitID = oo.VisitID
** left outer join DRSVisitEvents ave on av.VisitID = ave.VisitID**
** Inner join DRSCodesData dcd on dcd.VisitID = av.VisitID **
inner join DRSCodes dc on ( dc.CodeID = dcd.CodeID and dc.GrouperVersionID IN (select max(GrouperVersionID) from DRSCodes drc where dcd.CodeID=drc.CodeID))

First 3 joins working fine... but when I add last join It's not puling null values of DRSCodesData.CodeID .. For few accounts these values are NULL..

Please can any one help me.. what I am doing wrong on this ?

Thanks,
Archana


#8

Please provide

  • table description in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide
  • your query
  • the output your query generates from the sample data you provide