SQL outer join on three tables

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

SQL Server Development (2000)

SQL outer join on three tables

Author

Topic

akalehzan
Starting Member

21 Posts

Posted - 2007-12-07 : 17:52:55

Hi All,

USing SQL server 2000.
We have three tables:
ID Table
Subj_num
165
1364
1406
3841
3845
4149
4176

BEH_WISC_III.WSC Table
Subj_num WSC_WICH_STUDY
165 TravelT1
165 FraXimagingT1
1364 TravelT1 FraXimagingT1
1364 FraXimagingT2
1364 TravelT2
1406 TravelT1
3841 TravelT1
3841 TravelT2
3845 TravelT1
4149 TravelT1
4149 TravelT2
4176 TravelT1
4176 TravelT2

Beh_WAIS_III table
Subj_num WAS_which_study
165 TravelT2
1406 TravelT2
3845 TravelT2

Using the following script:



select dbo.BEH_WISC_III.*, BEH_WISC_III.subj_num as WISC_III_SUbj_num,  Beh_WAIS_III .*, beh_wais_III.subj_num as WAIS_III_Subj_num, id.subj_num as ID_SUBJ_NUM
from id left Outer join Beh_WAIS_III on id.subj_num = beh_WAIS_III.subj_num
	left Outer join BEH_WISC_III on id.subj_num = BEH_WISC_III.subj_num
where id.subj_num in (165, 1364, 1406, 3841, 3845, 4149, 4176) 

and WAS_which_study like '%T2'
and WAS_which_study like '%T1'
or WAS_which_study  is null
and WSC_which_study like '%T2'
and WSC_which_study like '%T1'
or WSC_which_study  is null

Some how it returns all the record from ID table like 10000 records.
I just want to see Subj_nums and

and WAS_which_study like '%T2'
and WAS_which_study like '%T1'
or WAS_which_study is null
and WSC_which_study like '%T2'
and WSC_which_study like '%T1'
or WSC_which_study is null

Thanks for any help

Abrahim

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-07 : 17:58:23

where id.subj_num in (165, 1364, 1406, 3841, 3845, 4149, 4176)
where id.subj_num in (165, 1364, 1406, 3841, 3845, 4149, 4176)
and WAS_which_study like '%T2'
and WAS_which_study like '%T1')
or (WAS_which_study is null
and WSC_which_study like '%T2'
and WSC_which_study like '%T1')
or WSC_which_study is null

you need a few parenthesis it looks like.

so it is Where () OR () or ()

Poor planning on your part does not constitute an emergency on my part.

akalehzan
Starting Member

21 Posts

Posted - 2007-12-07 : 23:06:35

Thanks dataguru1971

Abrahim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources