Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

complex joins retrive the echivalent results Expand / Collapse
Author
Message
Posted Sunday, July 14, 2013 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 1:55 PM
Points: 8, Visits: 25
i want the result for which :
table GLMSO which has the
gmso colum 0003 and has both of bot
of KCTR's in GLCTA table

06 Eme3Prodcat2x
07 Eme4Prod
Post #1473356
Posted Sunday, July 14, 2013 5:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 1,611, Visits: 16,464
Beniamin Joke (7/14/2013)
i want the result for which :
table GLMSO which has the
gmso colum 0003 and has both of bot
of KCTR's in GLCTA table

06 Eme3Prodcat2x
07 Eme4Prod



SELECT DISTINCT
GLPSI.IDN AS GLMSO_GLPSI_IDN,
GLMSO.IDN AS GLMSO_IDN,
-- GLMSO.KMSO,
GLMSO.FAB,
GLMSO.DFINV,
GLMSO.DFINA,
GLCTA.KCTR,
GLCTA.DCAT
FROM GLMSO
INNER JOIN GLARA ON GLARA.IDN = GLMSO.IDN
AND GLARA.KARA = GLMSO.KARA
INNER JOIN GLPSI ON GLPSI.IDN = GLARA.IDN
AND GLPSI.KPSI = GLARA.KPSI
INNER JOIN GLCMS ON GLCMS.IDN = GLMSO.IDN
AND GLCMS.KMSO = GLMSO.KMSO
INNER JOIN GLCVA ON GLCMS.IDN = GLCVA.IDN
AND GLCMS.KCVA = GLCVA.KCVA
INNER JOIN GLCTR ON GLCVA.IDN = GLCTR.IDN
AND GLCVA.KCTR = GLCTR.KCTR
INNER JOIN GLCTA ON GLMSO.IDN = GLCTA.IDN
AND GLMSO.KARA = GLCTA.KARA
WHERE ( GLPSI.IDN = '0099' )
AND ( GLMSO.FAB = 'A' )


SELECT DISTINCT
GLPSI.IDN AS GLMSO_GLPSI_IDN,
GLMSO.IDN AS GLMSO_IDN,
GLMSO.KMSO,
GLMSO.FAB,
GLMSO.DFINV,
GLMSO.DFINA,
GLCTA.KCTR,
GLCTA.DCAT
FROM GLMSO
INNER JOIN GLARA ON GLARA.IDN = GLMSO.IDN
AND GLARA.KARA = GLMSO.KARA
INNER JOIN GLPSI ON GLPSI.IDN = GLARA.IDN
AND GLPSI.KPSI = GLARA.KPSI
INNER JOIN GLCMS ON GLCMS.IDN = GLMSO.IDN
AND GLCMS.KMSO = GLMSO.KMSO
INNER JOIN GLCVA ON GLCMS.IDN = GLCVA.IDN
AND GLCMS.KCVA = GLCVA.KCVA
INNER JOIN GLCTR ON GLCVA.IDN = GLCTR.IDN
AND GLCVA.KCTR = GLCTR.KCTR
INNER JOIN GLCTA ON GLMSO.IDN = GLCTA.IDN
AND GLMSO.KARA = GLCTA.KARA
WHERE ( GLPSI.IDN = '0099' )
AND ( GLMSO.FAB = 'A' )
AND ( GLMSO.KMSO = '0003' )




__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1473358
Posted Sunday, July 14, 2013 1:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 1:55 PM
Points: 8, Visits: 25
Hi
the expected result should be where the gmso colums is equal to 003 from GLMSO table and which have in table GLCTA the column KCTR equal to 01 and 02
Post #1473381
Posted Sunday, July 14, 2013 1:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 1,611, Visits: 16,464
Beniamin Joke (7/14/2013)
Hi
the expected result should be where the gmso colums is equal to 003 from GLMSO table and which have in table GLCTA the column KCTR equal to 01 and 02


I don't see a "gmso" column in table GLMSO??

your setup code

create table GLMSO
(
IDN char(4),
KMSO char(4),
KMSOP char(6) null,
KARA char(2),
COD varchar(30) null,
NOM varchar (30) null,
DNAS char(8),
DINIV char(8),
DFINV char(8),
DFINA char(8),
FAB char(1),
UPS char(6),
UPD char(12),
KTDM char(2) not null


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1473382
Posted Sunday, July 14, 2013 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 1:55 PM
Points: 8, Visits: 25
sorry it's kmso the column and not gmso ,i am a little sleepy
Post #1473383
Posted Sunday, July 14, 2013 2:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 1,611, Visits: 16,464
Beniamin Joke (7/14/2013)
sorry it's kmso the column and not gmso ,i am a little sleepy


ok...sleep well

try running the "simplified" queries I posted earlier, when you are able.


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1473384
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse