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 Saturday, July 13, 2013 2:58 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
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
Post #1473287
Posted Saturday, July 13, 2013 11:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 3,305, Visits: 7,133
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473304
Posted Saturday, July 13, 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
Hi Luis
The dld statements you can find in here

http://www.fileconvoy.com/gf.php?id=g53d69455dc37d574999328655.3388215f1bc80ce1f414d4&sts=137374678179117331566955209e7df68c845596894d4148615d

Thanks
Post #1473312
Posted Saturday, July 13, 2013 2:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,872, Visits: 18,428
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 !
__________________________________________________________________
Post #1473313
Posted Saturday, July 13, 2013 2:22 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
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'


Post #1473314
Posted Saturday, July 13, 2013 2:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 3,305, Visits: 7,133
Thank you for the sample data, however, we're still missing the information for table GLCTA.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473315
Posted Saturday, July 13, 2013 2:30 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
Hello I've updated in my previous comment
Post #1473316
Posted Sunday, July 14, 2013 12:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,872, Visits: 18,428
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 !
__________________________________________________________________
Post #1473345
Posted Sunday, July 14, 2013 12:39 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
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'

Post #1473347
Posted Sunday, July 14, 2013 3:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,872, Visits: 18,428
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 !
__________________________________________________________________
Post #1473353
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse