need help sql minus - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

need help sql minus

Author

Topic

vynx
Starting Member

8 Posts

Posted - 2004-12-30 : 21:27:19

table1
No_REC QTY
1 100
2 500
3 600
4 250
5 400

table2
NO_JR QTY
1 50
3 10
3 40
5 100

i want to make query like this:

NO_TOTAL QTY
1 50
2 500
3 550
4 250
5 300

where result is from table1 minus table2 and table1.no_rec = table2.no_jr

some one can help me make this query?
thanks

TimS
Posting Yak Master

198 Posts

Posted - 2004-12-30 : 21:52:03

CREATE TABLE #table1 (No_REC INT PRIMARY KEY, QTY INT)
CREATE TABLE #table2 (NO_JR INT, QTY INT)

INSERT INTO #table1 (No_REC, QTY) VALUES (1, 100)
INSERT INTO #table1 (No_REC, QTY) VALUES (2, 500)
INSERT INTO #table1 (No_REC, QTY) VALUES (3, 600)
INSERT INTO #table1 (No_REC, QTY) VALUES (4, 250)
INSERT INTO #table1 (No_REC, QTY) VALUES (5, 400)

INSERT INTO #table2 (NO_JR, QTY) VALUES (1, 50)
INSERT INTO #table2 (NO_JR, QTY) VALUES (3, 10)
INSERT INTO #table2 (NO_JR, QTY) VALUES (3, 40)
INSERT INTO #table2 (NO_JR, QTY) VALUES (5, 100)

/*
i want to make query like this:

NO_TOTAL QTY
1 50
2 500
3 550
4 250
5 300

*/

SELECT s.No_REC, s.QTY - COALESCE(X1.MQTY,0) AS QTY
FROM #table1 s
LEFT JOIN
(
SELECT NO_JR, SUM(QTY) AS MQTY
FROM #table2 m
GROUP BY NO_JR
) AS X1 ON s.No_REC = X1.NO_JR

DROP TABLE #table1
DROP TABLE #table2

vynx
Starting Member

8 Posts

Posted - 2004-12-30 : 22:15:13

oops sorry, my data in table1 is like that
No_REC QTY
1 100
1 50
2 500
3 600
4 250
5 400

and result like

NO_TOTAL QTY
1 100
2 500
3 550
4 250
5 300

TimS
Posting Yak Master

198 Posts

Posted - 2004-12-31 : 09:45:51

CREATE TABLE #table1 (No_REC INT, QTY INT)
CREATE TABLE #table2 (NO_JR INT, QTY INT)

INSERT INTO #table1 (No_REC, QTY) VALUES (1, 50)
INSERT INTO #table1 (No_REC, QTY) VALUES (1, 100)
INSERT INTO #table1 (No_REC, QTY) VALUES (2, 500)
INSERT INTO #table1 (No_REC, QTY) VALUES (3, 600)
INSERT INTO #table1 (No_REC, QTY) VALUES (4, 250)
INSERT INTO #table1 (No_REC, QTY) VALUES (5, 400)

INSERT INTO #table2 (NO_JR, QTY) VALUES (1, 50)
INSERT INTO #table2 (NO_JR, QTY) VALUES (3, 10)
INSERT INTO #table2 (NO_JR, QTY) VALUES (3, 40)
INSERT INTO #table2 (NO_JR, QTY) VALUES (5, 100)
INSERT INTO #table2 (NO_JR, QTY) VALUES (6, 100)

SELECT X2.No_REC, X2.PQTY - COALESCE(X1.MQTY,0) AS QTY
FROM
(
SELECT No_REC, SUM(p.QTY) AS PQTY
FROM #table1 p
GROUP BY No_REC
) AS X2
LEFT JOIN
(
SELECT NO_JR, SUM(QTY) AS MQTY
FROM #table2 m
GROUP BY NO_JR
) AS X1 ON X2.No_REC = X1.NO_JR

-- OR

SELECT COALESCE(X2.No_REC,X1.NO_JR), COALESCE(X2.PQTY,0) - COALESCE(X1.MQTY,0) AS QTY
FROM
(
SELECT No_REC, SUM(p.QTY) AS PQTY
FROM #table1 p
GROUP BY No_REC
) AS X2
FULL OUTER JOIN
(
SELECT NO_JR, SUM(QTY) AS MQTY
FROM #table2 m
GROUP BY NO_JR
) AS X1 ON X2.No_REC = X1.NO_JR

DROP TABLE #table1
DROP TABLE #table2

Tim S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources