A problem regarding join .. - 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

SQL Server Development (2000)

A problem regarding join ..

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2006-02-08 : 06:13:44

select
csd1.csd_altr, csd1.csd_labl, csd1.csd_issu,
csd2.csd_altr, tra1.tra_part, csd2.csd_issu,
trd1.trd_recd, tra1.tra_type,
apr1.apr_reqd, apr1.apr_trn2, apr1.apr_stat
from
e0437dct dct1,
e0437csd csd1,
e0437tra tra1 left outer join e0437apr apr1
on
apr1.apr_docu = tra1.tra_drgn and
apr1.apr_part = tra1.tra_part and
apr1.apr_type = tra1.tra_type,
e0437csd csd2,
e0437trd trd1
where
dct1.dct_corr <> true and
dct1.dct_code = csd1.csd_type and
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi = 'BSCULMBD0010-' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_part = tra1.tra_part and
trd1.trd_type = tra1.tra_type

Output I get now with the above query is fine except few things as shown in bold letters ..
For which I again need to refer to CSD Table for the value I get as of now like "ALSUBTRT00250" and "PATRANST02500" and
get the respective csd_issu and similarly refer to TRD Table and get trd_recd date for the same ..
same with APC Table to get apc_libe with reference of apr_stat ...

Output I get with the above query as of now is as below:
Item csd_altr csd_labl csd_issu csd_altr_1 tra_part csd_issu_1 trd_recd tra_type apr_reqd apr_trn2 apr_stat
1 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO YAKUB) ALGA 18/02/2006 20/02/2006 I
2 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SUBCONT) ALGA 30/01/2006 31/01/2006 A 12/02/2006 ALSUBTRT00250 A
3 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 AL(TO BESIX) BESIX 03/02/2006 04/02/2006 I
4 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 PA(TO BESIX) BESIX 15/02/2006 16/02/2006 I
5 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) FG 30/01/2006 31/01/2006 I
6 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) FG 30/01/2006 30/01/2006 I
7 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) HS 30/01/2006 30/01/2006 I
8 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) KT 30/01/2006 I
9 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) MATIZ 30/01/2006 I
10 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) PARSO 30/01/2006 31/01/2006 A 14/02/2006 PATRANST02500 B
11 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO YAKUB) RECOR 18/02/2006 18/02/2006 F
12 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SUBCONT) RECOR 30/01/2006 31/01/2006 F
13 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) RECOR 30/01/2006 30/01/2006 F
14 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) YAKUB 30/01/2006 31/01/2006 I
15 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO YAKUB) YAKUB 18/02/2006 18/02/2006 C A

Output I get need
Item csd_altr csd_labl csd_issu csd_altr_1 tra_part csd_issu_1 trd_recd tra_type apr_reqd apr_trn2 trd_recd apr_stat
1 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO YAKUB) ALGA 18/02/2006 20/02/2006 I
2 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SUBCONT) ALGA 30/01/2006 31/01/2006 A 12/02/2006 AL(TO BESIX) 04/02/2006 Approved
3 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 AL(TO BESIX) BESIX 03/02/2006 04/02/2006 I
4 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 PA(TO BESIX) BESIX 15/02/2006 16/02/2006 I
5 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) FG 30/01/2006 31/01/2006 I
6 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) FG 30/01/2006 30/01/2006 I
7 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) HS 30/01/2006 30/01/2006 I
8 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) KT 30/01/2006 I
9 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) MATIZ 30/01/2006 I
10 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) PARSO 30/01/2006 31/01/2006 A 14/02/2006 PA(TO BESIX) 16/02/2006 Approved as Noted
11 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO YAKUB) RECOR 18/02/2006 18/02/2006 F
12 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SUBCONT) RECOR 30/01/2006 31/01/2006 F
13 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO PARSO) RECOR 30/01/2006 30/01/2006 F
14 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO SITE) YAKUB 30/01/2006 31/01/2006 I
15 BS/CUL-MB/0010- TEST DOCUMENT 30/01/2006 BS(TO YAKUB) YAKUB 18/02/2006 18/02/2006 C Approved

Table Entries are as follows

DCT Table
Item DCT_CODE DCT_LIBE DCT_CORR
1 D SHOP DRAWINGS False
2 S MATERIAL SUBMITTAL False
3 M METHOD STATEMENT False
4 T TRANSMITTAL True

CSD Table
Item CSD_ORIG CSD_SUBJ CSD_TYPE CSD_NUMB CSD_REVI CSD_LABL CSD_STAGE CSD_ISSU CSD_ALTR CSD_REQD CSD_OWNR CSD_TDES CSD_ACLAS
1 AL SUBTR T 0025 0 TRANSMITTAL 03/02/2006 AL(TO BESIX) ALGA T
2 BS CULMB D 0010 - TEST DOCUMENT X 30/01/2006 BS/CUL-MB/0010- BESIX TRIAL D
3 BS OFFTR T 0050 0 TRANSMITTAL 30/01/2006 BS(TO SITE) BESIX T
4 BS OFFTR T 0060 0 TRANSMITTAL 18/02/2006 BS(TO YAKUB) BESIX T
5 BS SUBTR T 0025 0 TRANSMITTAL 30/01/2006 BS(TO SUBCONT) 12/02/2006 BESIX T
6 BS TRANS T 0250 0 TRANSMITTAL 30/01/2006 BS(TO PARSO) BESIX T
7 PA TRANS T 0250 0 TRANSMITTAL 15/02/2006 PA(TO BESIX) PARSO T

TRA Table
Item TRA_TRNO TRA_DRGN TRA_MEDI TRA_COPY TRA_APPR TRA_STAG TRA_PART TRA_TYPE
1 BSOFFTRT00600 BSCULMBD0010- 0 False X ALGA I
2 BSSUBTRT00250 BSCULMBD0010- PR 1 True X ALGA A
3 ALSUBTRT00250 BSCULMBD0010- PR 1 False X BESIX I
4 PATRANST02500 BSCULMBD0010- A3 1 False X BESIX I
5 BSOFFTRT00500 BSCULMBD0010- A3 1 False X FG I
6 BSTRANST02500 BSCULMBD0010- 0 False X FG I
7 BSTRANST02500 BSCULMBD0010- 0 False X HS I
8 BSOFFTRT00500 BSCULMBD0010- 0 False X KT I
9 BSOFFTRT00500 BSCULMBD0010- 0 False X MATIZ I
10 BSTRANST02500 BSCULMBD0010- PR 1 True X PARSO A
11 BSOFFTRT00600 BSCULMBD0010- 0 False X RECOR F
12 BSSUBTRT00250 BSCULMBD0010- 0 False X RECOR F
13 BSTRANST02500 BSCULMBD0010- 0 False X RECOR F
14 BSOFFTRT00500 BSCULMBD0010- 0 False X YAKUB I
15 BSOFFTRT00600 BSCULMBD0010- PR 3 False X YAKUB C

TRD Table
Item TRD_TRNO TRD_PART TRD_COPY TRD_MEDI TRD_TYPE TRD_CC TRD_RECD
1 BSOFFTRT00600 ALGA 1 A3 I 1 20/02/2006
2 BSSUBTRT00250 ALGA 1 PR A 0 31/01/2006
3 ALSUBTRT00250 BESIX 1 PR I 0 04/02/2006
4 PATRANST02500 BESIX 1 A3 I 0 16/02/2006
5 BSOFFTRT00500 FG 1 A3 I 0 31/01/2006
6 BSTRANST02500 FG 1 A3 I 1 30/01/2006
7 BSTRANST02500 HS 1 A3 I 1 30/01/2006
8 BSOFFTRT00500 KT 1 A3 I 1
9 BSOFFTRT00500 MATIZ 1 A3 I 1
10 BSTRANST02500 PARSO 1 PR A 0 31/01/2006
11 BSOFFTRT00600 RECOR 1 A3 F 1 18/02/2006
12 BSSUBTRT00250 RECOR 1 A3 F 1 31/01/2006
13 BSTRANST02500 RECOR 1 A3 F 1 30/01/2006
14 BSOFFTRT00500 YAKUB 1 A3 I 1 31/01/2006
15 BSOFFTRT00600 YAKUB 3 PR C 0 18/02/2006

APR Table
Item APR_DOCU APR_PART APR_TYPE APR_TRN2 APR_STAT APR_REQD
1 BSCULMBD0010- ALGA A ALSUBTRT00250 A 12/02/2006
2 BSCULMBD0010- PARSO A PATRANST02500 B 14/02/2006
3 BSCULMBD0010- YAKUB C A

APC Table
Item APC_CODE APC_LIBE APC_VALUE
1 A APPROVED 1
2 B APPROVED AS NOTED 2
3 C REVISE AND RESUBMIT 3
4 D REJECTED 4
5 E MORE INFO REQUESTED 5
6 F QUERY ANSWERED 6
7 G SAMPLE IS REQUIRED 7
8 h SUPERSEDED 8

Now is that gonna help you to give me a hint to solve the problem ...
How do i do the further join ..

Neeraj

nssjari
Starting Member

46 Posts

Posted - 2006-02-08 : 06:24:29

Actually when i am getting confused with this text format posting ..
So was asking for ur email id .. anyways .. Is this helping you to get the problem ..

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

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

Hi,
Table and column names are really confusing..
anyways..
you can include ur Tables APC , TRD and CSD in the join

Hope this helps..
select
csd1.csd_altr, csd1.csd_labl, csd1.csd_issu,
csd2.csd_altr, tra1.tra_part, csd2.csd_issu,
trd1.trd_recd, tra1.tra_type,
apr1.apr_reqd, ~~apr1.apr_trn2, apr1.apr_stat~~ CSD_ORIG+csd_issu, trd.trd_recdate, apc.apc_libe
from
e0437dct dct1,
e0437csd csd1,
e0437tra tra1 left outer join e0437apr apr1
on
apr1.apr_docu = tra1.tra_drgn and
apr1.apr_part = tra1.tra_part and
apr1.apr_type = tra1.tra_type,
e0437csd csd2,
e0437trd trd1
,
csd,
apc,trd

where
dct1.dct_corr <> true and
dct1.dct_code = csd1.csd_type and
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi = 'BSCULMBD0010-' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_part = tra1.tra_part and
trd1.trd_type = tra1.tra_type

and apr1.apr_stat = apc.apc_code
and apr1.apr_trn2 = trd.TRD_TRNO
and csd.CSD_ORIG+csd.CSD_SUBJ+csd.CSD_TYPE +csd.CSD_NUM = apr1.apr_trn2

nssjari
Starting Member

46 Posts

Posted - 2006-02-08 : 08:18:37

Shalu here is the Code as u said ..
I already tried it ...

select
csd1.csd_altr, csd1.csd_issu,
csd2.csd_altr, tra1.tra_part, csd2.csd_issu,
trd1.trd_recd, tra1.tra_type,
apr1.apr_reqd, csd3.csd_altr, csd3.csd_issu, trd2.trd_recd, apc1.apc_libe
from
e0437dct dct1,
e0437csd csd1,
e0437tra tra1 left outer join e0437apr apr1
on
apr1.apr_docu = tra1.tra_drgn and
apr1.apr_part = tra1.tra_part and
apr1.apr_type = tra1.tra_type,
e0437csd csd2,
e0437trd trd1,
e0437csd csd3,
e0437trd trd2,
e0437apc apc1
where
dct1.dct_corr <> true and
dct1.dct_code = csd1.csd_type and
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi = 'BSCULMBD0010-' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_part = tra1.tra_part and
trd1.trd_type = tra1.tra_type and
apr1.apr_trn2 = csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi and
apr1.apr_trn2 = trd2.trd_trno and
apr1.apr_stat = apc1.apc_code

This would just filter out those two replied documents.
whereas i need all document with replied info along with replied ones ..

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-08 : 23:23:53

put a left outer join with these tables instead of inner join...
Hope this helps..
select a.csd_altr, a.csd_labl, a.csd_issu,
a.csd_altr, a.tra_part, a.csd_issu,
a.trd_recd, a.tra_type,
a.apr_reqd, csd.CSD_ORIG+csd.csd_issu, trd.trd_recdate, apc.apc_libe
from
(
select
csd1.csd_altr, csd1.csd_labl, csd1.csd_issu,
csd2.csd_altr, tra1.tra_part, csd2.csd_issu,
trd1.trd_recd, tra1.tra_type,
apr1.apr_reqd, apr1.apr_trn2, apr1.apr_stat
from
e0437dct dct1,
e0437csd csd1,
e0437tra tra1 left outer join e0437apr apr1
on
apr1.apr_docu = tra1.tra_drgn and
apr1.apr_part = tra1.tra_part and
apr1.apr_type = tra1.tra_type,
e0437csd csd2,
e0437trd trd1
where
dct1.dct_corr <> true and
dct1.dct_code = csd1.csd_type and
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi = 'BSCULMBD0010-' and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_part = tra1.tra_part and
trd1.trd_type = tra1.tra_type
) a
left outer join
csd
on csd.CSD_ORIG+csd.CSD_SUBJ+csd.CSD_TYPE +csd.CSD_NUM = a.apr_trn2
left outer join
apc
on
a.apr_stat = apc.apc_code
left outer join
trd
on
a.apr_trn2 = trd.TRD_TRNO

nssjari
Starting Member

46 Posts

Posted - 2006-02-09 : 00:52:44

I will try for this but i feel it would be really fine if i can have your email id to interact for a while on this please.

Neeraj

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-09 : 00:57:07

Hi Neeraj,
You can write down ur issue over here as i cannot access my personal mail id.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 01:14:52

quote:
Originally posted by nssjari

I will try for this but i feel it would be really fine if i can have your email id to interact for a while on this please.

Neeraj

You will have more response to your problem if you post it in the forum as there will be more than 1 pair of eyes looking at it.

----------------------------------
'KH'

nssjari
Starting Member

46 Posts

Posted - 2006-02-09 : 01:22:21

The last query which u have wrote seems to be complicated .. don't u feel so ..
May be I am not getting it right ..

What i get as of now
BS/CUL-MB/0010- > document altr no
TEST DOCUMENT > document title
30/01/2006 > document issue date
BS(TO SUBCONT) > transmittal altr number
ALGA > transmittal submitted to party
30/01/2006 > transmittal issue date
31/01/2006 > transmittal recd date
A > transmittal submitted for -- Approval "A"
12/02/2006 > reply expected by date
ALSUBTRT00250 > reply transmittal no > apr_trn2 > csd_orig+subj+type+numb+revi > csd_altr
03/02/2006 > reply issue date > csd_issu
04/02/2006 > reply recd date > trd_recd
A > reply status > apr_stat > apc_code > apc_libe

What output i need is like :
BS/CUL-MB/0010-
TEST DOCUMENT
30/01/2006
BS(TO SUBCONT)
ALGA
30/01/2006
31/01/2006
A
12/02/2006

AL(TO BESIX) -- here i need to refer to csd once more
03/02/2006 -- this is csd issue date with record corr to AL(TO BESIX) as csd_altr
04/02/2006 -- this is trans recd date ie trd_recd for a recor corr to ALSUBTRT00250
Approved -- this is apc_libe corr to apc_code ie apr_stat, which is "A"

Thanks for being so nice ..
I really appreciate ur kind help ..
Neeraj

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-09 : 02:03:14

Whats the output you are getting using the query?

nssjari
Starting Member

46 Posts

Posted - 2006-02-09 : 02:13:13

I am unable to execute this query ... so no output for it ... so far ..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 02:13:35

you can apply this approach



select csd_altr, csd_labl, csd_issu,
	csd_altr, tra1.tra_part, csd_issu, 
	trd_recd, tra_type,
	apr_reqd, apr_trn2, 
	case when apr_stat ='Somevalue' then 'yourvalue' else 'othervalue' end as apr_stat
from
(
	select
	csd1.csd_altr, csd1.csd_labl, csd1.csd_issu,
	csd2.csd_altr, tra1.tra_part, csd2.csd_issu, 
	trd1.trd_recd, tra1.tra_type,
	apr1.apr_reqd, apr1.apr_trn2, apr1.apr_stat 
	from
	e0437dct dct1,
	e0437csd csd1,
	e0437tra tra1 left outer join e0437apr apr1
	on
	apr1.apr_docu = tra1.tra_drgn and
	apr1.apr_part = tra1.tra_part and
	apr1.apr_type = tra1.tra_type,
	e0437csd csd2,
	e0437trd trd1
	where
	dct1.dct_corr <> true and
	dct1.dct_code = csd1.csd_type and
	csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi = 'BSCULMBD0010-' and
	tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
	csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
	trd1.trd_trno = tra1.tra_trno and
	trd1.trd_part = tra1.tra_part and
	trd1.trd_type = tra1.tra_type 
) T

Madhivanan

Failing to plan is Planning to fail

nssjari
Starting Member

46 Posts

Posted - 2006-02-10 : 23:43:20

Thanks ..
I will try this ..

Neeraj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources