i have problem with some query

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

General SQL Server Forums

New to SQL Server Programming

i have problem with some query

Author

Topic

wilang
Starting Member

12 Posts

Posted - 2009-07-31 : 02:00:42

hi,i have problem when solving query,i have 6 table(A,B,C,D,E,F) with same field.

TABEL A
ID1 IDPROD QTY
1 001 10
2 004 10
3 005 10
TABEL B
1 001 10
2 002 10
3 003 10

TABEL C
1 001 10
2 003 10
3 005 10

TABEL D
1 001 3
2 004 5
3 005 2

TABEL E
1 001 4
2 002 6
3 003 3

TABEL F
1 001 2
2 003 4
3 005 6

my task is to create query to show IDPROD,DEBET,KREDIT.
DEBET is sum qty ground on IDPROD from table A,B,C.
KREDIT is sum qty ground on IDPROD from table D,E,F.

so the result like this:

IDPROD DEBET KREDIT
001 30 9
002 10 6
003 20 7
004 10 5
005 20 8

can some one help me??

this is query to help you build tables:
create table a
( id1 varchar(10),
idprod varchar(10),
qty int
)
create table b
( id1 varchar(10),
idprod varchar(10),
qty int
)
create table c
( id1 varchar(10),
idprod varchar(10),
qty int
)
create table d
( id1 varchar(10),
idprod varchar(10),
qty int
)
create table e
( id1 varchar(10),
idprod varchar(10),
qty int
)
create table f
( id1 varchar(10),
idprod varchar(10),
qty int
)
insert into a values('1','001',10)
insert into a values('2','004',10)
insert into a values('3','005',10)
insert into b values('1','001',10)
insert into b values('2','002',10)
insert into b values('3','003',10)
insert into c values('1','001',10)
insert into c values('2','003',10)
insert into c values('3','005',10)

insert into d values('1','001',3)
insert into d values('2','004',5)
insert into d values('3','005',2)
insert into e values('1','001',4)
insert into e values('2','002',6)
insert into e values('3','003',3)
insert into f values('1','001',2)
insert into f values('2','003',4)
insert into f values('3','005',6)

W

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-31 : 02:15:19

[code]
SELECT IDPROD = i.idprod,
DEBET = ISNULL(a.qty, 0) + ISNULL(b.qty, 0) + ISNULL(c.qty, 0),
KREDIT = ISNULL(d.qty, 0) + ISNULL(e.qty, 0) + ISNULL(f.qty, 0)
FROM
(
SELECT idprod FROM a UNION
SELECT idprod FROM b UNION
SELECT idprod FROM c UNION
SELECT idprod FROM d UNION
SELECT idprod FROM e UNION
SELECT idprod FROM f
) i
LEFT JOIN a ON i.idprod = a.idprod
LEFT JOIN b ON i.idprod = b.idprod
LEFT JOIN c ON i.idprod = c.idprod
LEFT JOIN d ON i.idprod = d.idprod
LEFT JOIN e ON i.idprod = e.idprod
LEFT JOIN f ON i.idprod = f.idprod
[/code]

KH
[spoiler]Time is always against us[/spoiler]