Can anybody give a hint please ...

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)

Can anybody give a hint please ...

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2006-02-07 : 06:56:24

1) Table name: "tra"

csd_altr tra_part tra_type
BS/CUL-MB/0010- ALGA I
BS/CUL-MB/0010- ALGA A
BS/CUL-MB/0010- BESIX I
BS/CUL-MB/0010- BESIX I
BS/CUL-MB/0010- FG I
BS/CUL-MB/0010- FG I
BS/CUL-MB/0010- HS I
BS/CUL-MB/0010- KT I
BS/CUL-MB/0010- MATIZ I
BS/CUL-MB/0010- PARSO A
BS/CUL-MB/0010- RECOR F
BS/CUL-MB/0010- RECOR F
BS/CUL-MB/0010- RECOR F
BS/CUL-MB/0010- YAKUB I
BS/CUL-MB/0010- YAKUB C

2) Table name: "apr"

csd_altr tra_part tra_type apr_reqd apr_stat apr_trn2
BS/CUL-MB/0010- ALGA A 12/02/2006 A
BS/CUL-MB/0010- PARSO A 14/02/2006 B PATRANST02500

How can I join this two tables using a where clause if I need to have a output as below:

csd_altr tra_part tra_type apr_reqd apr_stat apr_trn2
BS/CUL-MB/0010- ALGA I
BS/CUL-MB/0010- ALGA A 12/02/2006 A
BS/CUL-MB/0010- BESIX I
BS/CUL-MB/0010- BESIX I
BS/CUL-MB/0010- FG I
BS/CUL-MB/0010- FG I
BS/CUL-MB/0010- HS I
BS/CUL-MB/0010- KT I
BS/CUL-MB/0010- MATIZ I
BS/CUL-MB/0010- PARSO A 14/02/2006 B PATRANST02500
BS/CUL-MB/0010- RECOR F
BS/CUL-MB/0010- RECOR F
BS/CUL-MB/0010- RECOR F
BS/CUL-MB/0010- YAKUB I
BS/CUL-MB/0010- YAKUB C

I can get this using a Left Outer Join .. I tried but I need to use a where clause .. in my query
Coz i have other tables as well to relate and having related where clauses ..

Could anybody help me with this very puzzle ... please

Thanks to all

Regards
Neeraj
neeraj.jariwala@gmail.com

nssjari
Starting Member

46 Posts

Posted - 2006-02-07 : 07:01:15

csd_altr, tra_part, tra_type, apr_reqd, apr_stat, apr_trn2

this are the column names if the display above is not very clear to understand

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-07 : 07:05:15

c if this helps..
select * from tra a
left outer join apr b
on
a.csd_altr = b.csd_altr
and a.tra_part = b.tra_part
and a.tra_type = b.tra_type
join <OtherTable> --specify join with other tables you want
on <join condition>
where
<where condition>

nssjari
Starting Member

46 Posts

Posted - 2006-02-07 : 07:09:48

Can I do something using this type of query instead of Keyword left outer join ...
But this one gives me some additional records ..

select distinct
tra_trno,
tra_drgn,
tra_part,
tra_type,
case
when
apr_part = tra_part and
apr_type = tra_type
then
apr_stat
end
from
e0437tra,
e0437apr
where
tra_drgn='BSCULMBD0010-' and
apr_docu = tra_drgn

Thanks a lot for ur info ..
I would try as u said ..

nssjari
Starting Member

46 Posts

Posted - 2006-02-07 : 07:16:06

hi .. but this does not work ..
It gives a wrong match of records ..

nssjari
Starting Member

46 Posts

Posted - 2006-02-07 : 07:20:14

Below query works but it gives all extra records of right side table tra
even if it has nothing to do with apr ..
I means records other than 'BSCULMBD0010-' .. are u getting this clear ..

Or else if u dont mind can i have ur email id so as to forward a file
with a clear picture of the problem only if u dont mind please.

SELECT *
FROM e0437tra LEFT OUTER JOIN e0437apr
ON
e0437tra.tra_drgn = 'BSCULMBD0010-' and
e0437tra.tra_drgn = e0437apr.apr_docu and
e0437tra.tra_part = e0437apr.apr_part and
e0437tra.tra_type = e0437apr.apr_type

e0437 is nothing but the database code (Project code)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 09:53:33

Try this also

Select T1.csd_altr, T1.tra_part, T1.tra_type,T2.apr_reqd.T2.apr_stat, T2.apr_trn2
from tra T1 left outer join apr T2 
	on T1.csd_altr=T2.csd_altr and T1.tra_part on T2.tra_part 
	and T1.tra_type=T1.tra_type 
where t1.tra_drgn = 'BSCULMBD0010-'

Madhivanan

Failing to plan is Planning to fail

nssjari
Starting Member

46 Posts

Posted - 2006-02-08 : 01:01:07

Hey .. Shallu and Madhivanan .. You both are great boss ..
It works dear ..

Thanks a lot to both of you ..
Madhivanan .. your way it gives me exactly what i need ..

Neeraj

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 02:14:17

Also read this on how to use Left joins
http://www.sqlteam.com/item.asp?ItemID=11122

Madhivanan

Failing to plan is Planning to fail

nssjari
Starting Member

46 Posts

Posted - 2006-02-08 : 03:34:08

Mr. Madhivanan .. Please if you can spare a moment more ..
I am at an edge of completion of my project work ..
Need one more small help of yours if u dont mind please ..

I need to interact with you on this ..
I request you .. I know you must be very busy .. still ..

Neeraj

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 05:14:16

Post the problem you are facing

Madhivanan

Failing to plan is Planning to fail

nssjari
Starting Member

46 Posts

Posted - 2006-02-08 : 06:16:43

I have posted the complete info as a new subject in same section .. "A problem with joins .."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources