July 13, 2013 at 2:58 am
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
July 13, 2013 at 11:59 am
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'
July 13, 2013 at 1:53 pm
Hi Luis
The dld statements you can find in here
Thanks
July 13, 2013 at 2:10 pm
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
July 13, 2013 at 2:22 pm
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'
July 13, 2013 at 2:29 pm
Thank you for the sample data, however, we're still missing the information for table GLCTA.
July 13, 2013 at 2:30 pm
Hello I've updated in my previous comment
July 14, 2013 at 12:37 am
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
July 14, 2013 at 12:39 am
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'
July 14, 2013 at 3:25 am
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
July 14, 2013 at 5:30 am
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
July 14, 2013 at 5:50 am
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
July 14, 2013 at 1:40 pm
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
July 14, 2013 at 1:50 pm
Beniamin Joke (7/14/2013)
Hithe 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
July 14, 2013 at 1:53 pm
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