JOIN Problem, please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

JOIN Problem, please help

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-08-05 : 00:32:32

Hello,

basically I have the following problem

I want to select B.projectDescription WHERE A.pojectID = B.projectID

but what happens if A.projectID IS NULL??? at the moment the whole querry is stopped since I join many tables and have AND clauses (see blue text)

So how should i change this query, that if tvrq0.trva_f_proj_id_code or tvrq0.trva_f_fund_id_code ARE NULL their "=" statements are just not executed and the values for projDesc or fundDesc simple remain NULL and the rest of the query is executed?

I think the syntax I used is not up to date, maybe I should use a left join, but I don't know how to do that

So I would realy apreciate your help since I am stuck here.

here a short version of my query:

SELECT
tvrq0.tvrq_doc_id,
itnh0.date,
proj0.projDesc,
fund0.fundDesc
FROM
tvrq0,
itnh0,
proj0,
fund0
WHERE
((tvrq0.tvrq_doc_id = itnh0.itnh_f_tvrq_doc_id)
AND (itnh0.itnh_f_tvrl_seq_num = (SELECT MAX(itnh0.itnh_f_tvrl_seq_num) FROM itnh0
WHERE tvrq0.tvrq_doc_id = itnh0.itnh_f_tvrq_doc_id)))
AND (tvrq0.trva_f_fund_id_code = fund0.fund_id_code)
AND (tvrq0.trva_f_proj_id_code = proj0.proj_id_code)
AND ((tvrq0.tvrq_apprv_trav_ind=1)
AND (tvrq0.tvrq_f_refx_trpn_seq_num=1)
AND (itnh0.itnh_deprt_date>={ts '2004-01-01 00:00:00'}))

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2004-08-05 : 00:44:54

Try using isnull function in the join. Or else u can also check with "is not null".

For e.g

AND (isnull(tvrq0.trva_f_fund_id_code,'a') = isnull(fund0.fund_id_code,'b'))
AND (isnull(tvrq0.trva_f_proj_id_code,a) = isnull(proj0.proj_id_code,'b'))

Best Regards
Sachin

vux
Starting Member

45 Posts

Posted - 2004-08-05 : 02:27:37

Hi Sachin,

doesn't isnull not just translate NULL values into the value you give as 2nd parameter?

in that case your query would set tvrq0.trva_f_fund_id_code to 'a' and deppending if fund0.fund_id_code ISNULL to 'b' - hence the "=" statement is false again

the query should simply leave the values that come from the fund or project table NULL if the relating value in the tvrq0 table IS NULL

vux
Starting Member

45 Posts

Posted - 2004-08-05 : 02:46:41

i am quite sure now that i have to rewrite the query as left join in SQL 92 syntax - hope it does not take hours for me...

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-05 : 02:56:35

Hope this helps
create table #x(id int,name varchar(10))
insert into #x values(1,'aa')
insert into #x values(2,'bb')
insert into #x values(3,'cc')
insert into #x values(4,'dd')

create table #y(id int,address varchar(20))
insert into #y values(1,'aa-address')
insert into #y values(2,'bb-address')
insert into #y values(3,'cc-address')
insert into #y values(5,'ee-address')

select #x.id,#x.name,#y.id,#y.address
from #x,#y where #x.id=#y.id

select #x.id,#x.name,#y.id,#y.address
from #x LEFT join #y on #x.id=#y.id

select #x.id,#x.name,#y.id,#y.address
from #x RIGHT join #y on #x.id=#y.id

select #x.id,#x.name,#y.id,#y.address
from #x FULL join #y on #x.id=#y.id

Choose whatever join suits your requirement.

otherwise post data structures and sample data.

Cheers

vux
Starting Member

45 Posts

Posted - 2004-08-05 : 03:27:01

HI,

just solved the left join, its working now very easy actually:

thx!

FROM
extracts.dbo.tvrq0_extract tvrq0_extract LEFT JOIN extracts.dbo.itnh0_extract itnh0_extract ON (tvrq0_extract.tvrq_doc_id = itnh0_extract.itnh_f_tvrq_doc_id)
LEFT JOIN extracts.dbo.fund0_extract fund0_extract ON (tvrq0_extract.trva_f_fund_id_code = fund0_extract.fund_id_code)
LEFT JOIN extracts.dbo.proj0_extract proj0_extract ON (tvrq0_extract.trva_f_proj_id_code = proj0_extract.proj_id_code)
WHERE
(itnh0_extract.itnh_f_tvrl_seq_num = (SELECT MAX(itnh0_extract.itnh_f_tvrl_seq_num) FROM itnh0_extract
WHERE tvrq0_extract.tvrq_doc_id = itnh0_extract.itnh_f_tvrq_doc_id))
AND
(tvrq0_extract.tvrq_apprv_trav_ind=1)
AND
(tvrq0_extract.tvrq_f_refx_trpn_seq_num=1)
AND
(itnh0_extract.itnh_deprt_date>={ts '2004-01-01 00:00:00'})

ffoiii
Starting Member

12 Posts

Posted - 2004-08-05 : 18:58:20

Are you _sure_ your query is working correctly? When doing a left join to a table and then setting a criteria for that table in the where clause, you effectively alter the left join into an inner join unless you specifically allow for a null value in the where clause for the left joined table.

ex:
select * from a left join b on a.id = b.id
where b.insertdate > '1/1/2004'

a null value in b.insertdate evaluates to false, regardless if the reason for the null value is an unmatched record in a or a matched record with a null value. thus the left join is effectively an inner join.

fix:
select * from a left join b on a.id = b.id
where (b.id is null or b.insertdate > '1/1/2004')

in your query, you left join to itnh0_extract and in two places in your where clause specify matching values for this object.

Two solutions are possible, move the criteria for itnh0_extract to the join clause, or change the where clause to specifically include nulls.

Personally, I prefer moving the criteria to the join clause when the criteria is not overly complicated.

FROM extracts.dbo.tvrq0_extract tvrq0_extract
LEFT JOIN (extracts.dbo.itnh0_extract itnh0_extract inner join (select tvrq_doc_id, max(itnh_f_tvrl_seq_num) maxseq
from extracts.dbo.itnh0_extract
group by tvrq_doc_id) maxseq
on maxseq.tvrq_doc_id = itnh0_extract.inth_f_tvrq_doc_id and maxseq.maxseq = itnh0_extract.itnh_f_tvrl_seq_num)
ON tvrq0_extract.tvrq_doc_id = itnh0_extract.itnh_f_tvrq_doc_id and itnh0_extract.itnh_deprt_dat >= {ts '2004-01-01 00:00:00'}
LEFT JOIN extracts.dbo.fund0_extract fund0_extract ON tvrq0_extract.trva_f_fund_id_code = fund0_extract.fund_id_code
LEFT JOIN extracts.dbo.proj0_extract proj0_extract ON tvrq0_extract.trva_f_proj_id_code = proj0_extract.proj_id_code
WHERE (tvrq0_extract.tvrq_apprv_trav_ind = 1)
AND (tvrq0_extract.tvrq_f_refx_trpn_seq_num=1)

ffoiii

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources