complex joins retrive the echivalent results

  • Hello

    I have a problem on doing an complex join between multiple tables :

    Here is my tables

    GLCMS

    IDN KMSO KCVA FSCVA

    ---- ---- ---- -----

    0099 0001 0007 N

    0099 0001 000D A

    0099 0003 0007 A

    0099 0003 000D A

    0099 0009 0007 A

    0099 000A 000D A

    GLMSO

    IDN KMSO KMSOP KARA COD NOM DNAS DINIV DFINV DFINA FAB UPS UPD KTDM

    ---- ---- ----- ---- -------------------- ------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- -------- ---- ---------------- ------------ ----

    0099 0003 NULL 01 asdasd asdasdas 00000000 00000000 99999999 99999999 A admin 201307101544 02

    0099 0006 NULL 02 ModuleSoftware2 NormeCondition2 00000000 00000000 99999999 99999999 A admin 201307101214 01

    0099 0007 NULL 01 Impersonaneeee Pendice 00000000 00000000 99999999 99999999 A admin 201307101230 01

    0099 0009 NULL 02 Prodologia 34234234234 00000000 00000000 99999999 99999999 A admin 201307101645 01

    0099 000A NULL 02 ProblemoFiltre Benj

    GLARA

    IDN KMSO KMSOP KARA COD NOM DNAS DINIV DFINV DFINA FAB UPS UPD KTDM

    ---- ---- ----- ---- -------------------- ------------------------------------------------------------------------------------------------------------------------ -------- -------- -------- -------- ---- ---------------- ------------ ----

    0099 0003 NULL 01 asdasd asdasdas 00000000 00000000 99999999 99999999 A admin 201307101544 02

    0099 0006 NULL 02 ModuleSoftware2 NormeCondition2 00000000 00000000 99999999 99999999 A admin 201307101214 01

    0099 0007 NULL 01 Impersonaneeee Pendice 00000000 00000000 99999999 99999999 A admin 201307101230 01

    0099 0009 NULL 02 Prodologia 34234234234 00000000 00000000 99999999 99999999 A admin 201307101645 01

    0099 000A NULL 02 ProblemoFiltre Benj

    GLPSI

    IDN KPSI NOM DNAS ORD FAB SIG UPS UPD DESCR COD NTE

    ---- ---- -------------------------------------------------------------------------------- -------- ----------- ---- -------------------- ---------------- ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    0099 01 Pacchetto 1 00000000 0 A EMEPS1 admin 201306121059 Il pacchetto 1 PS1 sdsdafdsa

    0099 02 Pacchetto 2 00000000 0 A EMEPS2 admin 201306121116 Questo è il pacchetto 2 PS2 NULL

    GLTDM

    IDN KTDM SIG NOM DTDM IMG FAB UPS UPD

    ---- ---- -------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- ---------------- ------------

    0099 01 MAR1 Marchio 1 Questo è il marchio 1 NULL A admin 201306121030

    0099 02 MAR2 Marchio 2 Questo è il marchio 2 0x89504E470D0A1A0A0000000D494844520000006000000048080200000086056734000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA86400003B2C49444154785E5D7C07785555BAF6997B71140B9D0001D27B4F486F1048EFBD27E7242727E79C9CDE A admin 201306121055

    GLCVA

    IDN KCVA KCTR DCVA FSCVA

    ---- ---- ---- -------------------------------------------------------------------------------- -----

    0099 0001 01 Distribuzione A

    0099 0002 01 Vendita A

    0099 0003 02 Energia A

    0099 0004 02 Acqua A

    0099 0005 02 Gas A

    0099 0007 07 aaa A

    0099 0008 06 v2 A

    0099 0009 08 aaaa A

    0099 000A 08 yyyyjjjjj A

    0099 000B 04 aa A

    0099 000C 04 bb A

    0099 000D 06 v3 A

    0099 000E 06 v4 A

    GLCTR

    IDN KCTR DCAT XMUL FSCTR FAP

    ---- ---- -------------------------------------------------------------------------------- ---- ----- ----

    0099 01 Settore X A C

    0099 02 Servizio X A C

    0099 03 EttoreCust1 NULL A C

    0099 04 EttoreCust2 X A C

    0099 06 Eme3Prodcat2x X A P

    0099 07 Eme4Prod NULL A P

    0099 08 sss X A P

    0099 0C AAA NULL A P

    Here is my sql query which is wrong on ,,AND ( SELECT COUNT(*) ..... '':

    DECLARE @kpsi AS VarChar (2)

    DECLARE @diniv AS VarChar (8)

    DECLARE @dfina AS VarChar (8)

    DECLARE @Fab AS VarChar (1)

    DECLARE @idn AS VarChar (4)

    SET @kpsi = '02'

    SET @diniv = '00000000'

    SET @dfina = '99999999'

    SET @Fab = 'A'

    SET @idn = '0099'

    select distinct

    GLPSI.IDN GLMSO_GLPSI_IDN, GLPSI.KPSI GLMSO_GLPSI_KPSI, GLPSI.SIG GLMSO_GLPSI_SIG, GLPSI.NOM GLMSO_GLPSI_NOM, GLPSI.DESCR GLMSO_GLPSI_DESCR, GLPSI.DNAS GLMSO_GLPSI_DNAS, GLPSI.ORD GLMSO_GLPSI_ORD, GLPSI.FAB GLMSO_GLPSI_FAB, GLPSI.NTE GLMSO_GLPSI_NTE, GLPSI.COD GLMSO_GLPSI_COD, GLPSI.UPS GLMSO_GLPSI_UPS, GLPSI.UPD GLMSO_GLPSI_UPD, GLARA.IDN GLMSO_GLARA_IDN, GLARA.KARA GLMSO_GLARA_KARA, GLARA.KPSI GLMSO_GLARA_KPSI, GLARA.SIG GLMSO_GLARA_SIG, GLARA.NOM GLMSO_GLARA_NOM, GLARA.DNAS GLMSO_GLARA_DNAS, GLARA.ORD GLMSO_GLARA_ORD, GLARA.FAB GLMSO_GLARA_FAB, GLARA.UPS GLMSO_GLARA_UPS, GLARA.UPD GLMSO_GLARA_UPD, GLMSO.IDN GLMSO_IDN, GLMSO.KMSO GLMSO_KMSO, GLMSO.KMSOP GLMSO_KMSOP, GLMSO.KARA GLMSO_KARA, GLMSO.COD GLMSO_COD, GLMSO.NOM GLMSO_NOM, GLMSO.DNAS GLMSO_DNAS, GLMSO.DINIV GLMSO_DINIV, GLMSO.DFINV GLMSO_DFINV, GLMSO.DFINA GLMSO_DFINA, GLMSO.FAB GLMSO_FAB, GLMSO.KTDM GLMSO_KTDM, GLMSO.UPS GLMSO_UPS, GLMSO.UPD GLMSO_UPD

    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

    where GLMSO.IDN = @idn

    AND GLMSO.DINIV >= @diniv

    AND GLMSO.DFINA <= @dfina

    AND GLMSO.FAB = @Fab

    AND ( SELECT COUNT(*) FROM

    (SELECT DISTINCT GL2.KCTR FROM GLCTA GL2 WHERE GL2.IDN = GLMSO.IDN

    AND GL2.KARA = GLMSO.KARA AND GL2.KCTR in ('06','07') ) qr) = 2

    ORDER BY GLMSO.COD

    The expected output result should be the row with gmso 0003 which has both

    of KCTR's

    06 Eme3Prodcat2x

    07 Eme4Prod

    Can some one pls help

  • Could you please post your sample data as DDL statements? I'll give you an example but right now, I don't have time to do it with all the tables. This way I can start to work directly on the problem and will identify any possible problems with datatypes.

    CREATE TABLE GLCMS(

    IDN char(4),

    KMSO char(4),

    KCVA char(4),

    FSCVA char(1))

    INSERT INTO GLCMS

    SELECT '0099', '0001', '0007', 'N' UNION ALL

    SELECT '0099', '0001', '000D', 'A' UNION ALL

    SELECT '0099', '0003', '0007', 'A' UNION ALL

    SELECT '0099', '0003', '000D', 'A' UNION ALL

    SELECT '0099', '0009', '0007', 'A' UNION ALL

    SELECT '0099', '000A', '000D', 'A'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • any reason why you cant post the DDL on this site as per normal posts?

    if you need help please ask

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Hello

    this was a fast way to share my query ,but i can paste here too.

    create table GLCTR

    (

    IDN char(4),

    KCTR char(2),

    DCAT varchar(50) not null,

    XMUL char(1) null,

    FSCTR char(1) null,

    FAP char(1) null

    )

    go

    insert into GLCTR

    select '0099','01','Settore','X','A','C' UNION ALL

    select '0099','02','Servizio','X','A','C' UNION ALL

    select '0099','03','EttoreCust1',null,'A','C' UNION ALL

    select '0099','04','EttoreCust2','X','A','C' UNION ALL

    select '0099','06','Eme3Prodcat2x','X','A','P' UNION ALL

    select '0099','07','Eme4Prod',null,'A','P' UNION ALL

    select '0099','08','sss','X','A','P' UNION ALL

    select '0099','0C','AAA',null,'A','P'

    go

    create table GLCVA

    (

    IDN char(4),

    KCVA char(4),

    KCTR char(2),

    DCVA varchar(50) not null,

    FSCVA char(1) null

    )

    go

    insert into GLCVA

    select '0099','0001','01','Distribuzione','A' UNION ALL

    select '0099','0002','01','Vendita','A' UNION ALL

    select '0099','0003','02','Energia','A' UNION ALL

    select '0099','0004','02','Acqua','A' UNION ALL

    select '0099','0005','02','Gas','A' UNION ALL

    select '0099','0007','07','aaa','A' UNION ALL

    select '0099','0008','06','v2','A' UNION ALL

    select '0099','0009','08','aaaa','A' UNION ALL

    select '0099','000A','08','yyyyjjjjj','A' UNION ALL

    select '0099','000B','04','aa','A' UNION ALL

    select '0099','000C','04','bb','A' UNION ALL

    select '0099','000D','06','v3','A' UNION ALL

    select '0099','000E','06','v4','A'

    go

    create table GLTDM

    (

    IDN char(4),

    KTDM char(2),

    SIG varchar(10),

    NOM varchar(50) not null,

    DTDM varchar(30) null,

    IMG image null

    )

    go

    insert into GLTDM

    select '0099','01','MAR1','Marchio1','Questoèilmarchio1', null

    UNION ALL

    select '0099','02','MAR2','Marchio2','Questoèilmarchio2', null

    go

    create table GLPSI

    (

    IDN char(4),

    KPSI char(2),

    NOM varchar(20),

    DNAS varchar(10),

    ORD char (1),

    FAB char (1),

    SIG char (7),

    UPS char (7),

    UPD varchar(12),

    DESCR varchar(30),

    COD char(3),

    NTE varchar(40) null

    )

    go

    insert into GLPSI

    select '0099','01','Pacchetto1','00000000','0','A','EMEPS1','admin','201306121059','Ilpacchetto1', 'PS1','sdsdafdsa'

    UNION ALL

    select '0099','02','Pacchetto2','00000000','0','A','EMEPS2','admin','201306121059','Ilpacchetto2', 'PS2',null

    go

    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

    )

    go

    insert into GLMSO

    select '0099','0003',null,'01','asdasd', 'asdasdas','00000000','00000000','99999999','99999999','A','admin','201307101544', '02'

    UNION ALL

    select '0099','0006',null,'02','ModuleSoftware2','NormeCondition2','00000000','00000000','99999999','99999999','A','admin','201307101214', '01'

    UNION ALL

    select '0099','0007',null,'01','Impersonaneeee','Pendice','00000000','00000000','99999999','99999999','A','admin','201307101230', '01'

    UNION ALL

    select '0099','0009',null,'02','Prodologia','34234234234','00000000','00000000','99999999','99999999','A','admin','201307101645', '02'

    go

    CREATE TABLE GLCMS

    (IDN char(4),

    KMSO char(4),

    KCVA char(4),

    FSCVA char(1)

    )

    go

    INSERT INTO GLCMS

    SELECT '0099', '0001', '0007', 'N' UNION ALL

    SELECT '0099', '0001', '000D', 'A' UNION ALL

    SELECT '0099', '0003', '0007', 'A' UNION ALL

    SELECT '0099', '0003', '000D', 'A' UNION ALL

    SELECT '0099', '0009', '0007', 'A' UNION ALL

    SELECT '0099', '000A', '000D', 'A'

    go

    create table GLARA

    (

    IDN char(4),

    KMSO char(4),

    KARA char(2),

    KPSI char(2)

    )

    go

    insert into GLARA

    select '0099','0003','01','01'

    union all

    select '0099','0003','02','01'

    union all

    select '0099','0006','01','02'

    union all

    select '0099','0007','02','01'

    go

    insert into GLCTA

    select '0099','01','06','Eme3Prodcat2x'

    union all

    select '0099','01','07','Eme4Prod'

    union all

    select '0099','02','07','Eme4Prod'

  • Thank you for the sample data, however, we're still missing the information for table GLCTA.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hello I've updated in my previous comment

  • Beniamin Joke (7/13/2013)


    Hello I've updated in my previous comment

    we need a CREATE TABLE statement for GLCTA.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • sorry,

    here

    create table GLCTA

    (

    IDN char(4),

    KARA char(2),

    KCTR char(2),

    DCAT varchar(50) not null

    )

    go

    insert into GLCTA

    select '0099','01','06','Eme3Prodcat2x'

    union all

    select '0099','01','07','Eme4Prod'

    union all

    select '0099','02','07','Eme4Prod'

  • in an effort to try and understand what you require, I have attempted to simplify your query and just return relevant columns....based on code below...what results do you want?

    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' )

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • 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

  • 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
    and remember....every day is a school day

  • 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

  • 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
    and remember....every day is a school day

  • sorry it's kmso the column and not gmso ,i am a little sleepy

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply