Joining tables to show one extra column

Transact-SQL
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
9

hi archana

this topic was 8 months
i am investigating it NOW

everybody must have forgotten

i hope it helps .. thanks
:slight_smile:
:slight_smile:

I think the problem was with the left outer join
you used on
left outer join DRSCodes dc on dcd.CodeID = dc.CodeID

i think this should have been " just join " ...

> join DRSCodes dc on dcd.CodeID = dc.CodeID

with left outer join
it returns a lot of records

with only join
very few records come

i created some sample test data

drop create sample test data 
use tempdb 

go 


drop table data1
go 

create table data1
(
id int null, 
name varchar(100) null
)
go 

insert into data1 select 1, 'pra'
insert into data1 select 2, 'har'
insert into data1 select 3, 'vij'
insert into data1 select 4, null
insert into data1 select 5, null
insert into data1 select 6, 'boo'
go 

use tempdb 

go 


drop table data2
go 

create table data2
(
id int null, 
address varchar(100) null 
)
go 

insert into data2 select 1, 'park avenue '
insert into data2 select 2, null
insert into data2 select 3, 'street five'
insert into data2 select 4, 'ok'
insert into data2 select 5, 'good ok top street '
insert into data2 select 6, 'v r nagar'


use tempdb 

go 


drop table data_Intermediate
go 

create table data_Intermediate
(
id int null,
salary int null,
month_sal int null  
)
go 

insert into data_Intermediate select 1, 100, 12
insert into data_Intermediate select 1, 67,10
insert into data_Intermediate select 1, 34,9 

insert into data_Intermediate select 2, 78, 7
insert into data_Intermediate select 2, 34,8
insert into data_Intermediate select 2, 47,null

insert into data_Intermediate select 3, null, 3
insert into data_Intermediate select 3, 88,2
insert into data_Intermediate select 3, null,null


use tempdb 

go 


drop table datadescription
go 

create table datadescription
(
month_sal int null ,
description varchar(100) null 
)
go 

insert into datadescription select 5,'ooooooo'
insert into datadescription select 8,'rr hh yy '
insert into datadescription select 10,'ggg jj '
insert into datadescription select 12,' kkk ddddddd ' 
go
SQL with LEFT OUTER join 
SELECT *, 
       d.description 
FROM   data1 a 
       LEFT OUTER JOIN data2 b 
                    ON a.id = b.id 
       LEFT OUTER JOIN data_intermediate c 
                    ON a.id = c.id 
       LEFT OUTER JOIN datadescription d 
                    ON c.month_sal = d.month_sal
Results with LEFT OUTER JOIN

image
image.png1365×663 217 KB

SQL no left outer join .. only join for datadescription table 
SELECT *, 
       d.description 
FROM   data1 a 
       LEFT OUTER JOIN data2 b 
                    ON a.id = b.id 
       LEFT OUTER JOIN data_intermediate c 
                    ON a.id = c.id 
       JOIN datadescription d 
                    ON c.month_sal = d.month_sal
Results SQL removing left outer .. for datadescription table

image
image.png1248×233 80.5 KB