Newbie Query Question - 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

General SQL Server Forums

New to SQL Server Programming

Newbie Query Question

Author

Topic

Otacustes
Starting Member

15 Posts

Posted - 2008-07-13 : 12:06:20

Hello all,

I am looking for some help with the following dilemma, please. I am sure these are quite simple but unfortunately the solutions escape me.

I have two tables set out like this:

Table A
===========
PartNo - Qty
GE0000 – 10
GE0001 – 20
GE0004 – 40
GE0001 – 20

Table B
===========
PartNo - Qty
GE0000 – 10
GE0001 – 20
GE0002 – 30

What I need help with is 3, maybe 4, queries so that:

Query 1:
Returns a list of all of the parts contained in both table A and table B with updated quantities, for example:
GE0000 – 20
GE0001 – 60
GE0002 – 30
GE0004 – 40
It is likely that there will be a PrevQty column included. Is it possible to update the PrevQty with the Table A Qty? Do you have to do two separate queries here or can they be nested into one query?

Query 2:
Returns a list of everything in table A that does not appear in table B with updated quantities, for example:
GE0004 – 40

Query 3:
Returns everything that does not appear in table A and table B with updated quantities, for example:
GE0002 – 40
GE0004 – 40

Query 4:
Returns a list of everything in table B that does not appear in table A with updated quantities, for example:
GE0002 – 30
The expression for this is likely to be the reverse of query 2, perhaps???

Thanks for your help.

Any help or guidance offered is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-13 : 13:21:54

SELECT t.PartNo,SUM(t.Qty) AS Qty
FROM(SELECT PartNo , Qty FROM TableA
     UNION ALL
     SELECT PartNo , Qty FROM TableB)t
GROUP BY t.PartNo
ORDER BY t.PartNo

SELECT PartNo,SUM(Qty) AS Qty
  FROM TableA
  WHERE PartNo NOT IN (SELECT PartNo FROM TableB)
  GROUP BY PartNo 
  ORDER BY PartNo

if you're sure the partno wont repeat in tablea itself you can avoid group by and sum.
3.

SELECT t.PartNo,MAX(t.Qty) AS Qty
FROM(SELECT PartNo , Qty FROM TableA
     UNION ALL
     SELECT PartNo , Qty FROM TableB)t
GROUP BY t.PartNo
HAVING COUNT(*) =1
ORDER BY t.PartNo

SELECT PartNo,SUM(Qty) AS Qty
  FROM TableB
  WHERE PartNo NOT IN (SELECT PartNo FROM TableA)
  GROUP BY PartNo 
  ORDER BY PartNo

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-13 : 13:29:10

Oh man - I can't believe you're doing their homework for them

And I bet the prof is more interested in seeing correct use of the basic JOIN types given those exact samples :)

Be One with the Optimizer
TG

Otacustes
Starting Member

15 Posts

Posted - 2008-07-14 : 15:33:59

Thanks for your help visakh16, it has been much appreciated.

TG you cheeky git

thats not my homework!! On a serious not it is actually for an ERP system interface.

Thanks again.

Any help or guidance offered is greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-15 : 08:18:34

"cheeky git" ? I'm going to have to break out my English/English(US) dictionary.

What do the tables A and B represent? It is unusual to have two different tables with identical structures - Could be indications of a bad design.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:34:55

And to really screw you around, all for queries can be written as one query only!

DECLARE	@TableA TABLE (PartNo CHAR(6), Qty TINYINT)

INSERT	@TableA
SELECT	'GE0000', 10 UNION ALL
SELECT	'GE0001', 20 UNION ALL
SELECT	'GE0004', 40 UNION ALL
SELECT	'GE0001', 20

DECLARE	@TableB TABLE (PartNo CHAR(6), Qty TINYINT)

INSERT	@TableB
SELECT	'GE0000', 10 UNION ALL
SELECT	'GE0001', 20 UNION ALL
SELECT	'GE0002', 30

SELECT		PartNo,
		SUM(Qty) AS Qty,
		1 AS Query1,
		MIN(CASE TableName WHEN 'TableA' THEN 1 ELSE 0 END) AS Query2,
		COUNT(DISTINCT TableName) % 2 AS Query3,
		MIN(CASE TableName WHEN 'TableB' THEN 1 ELSE 0 END) AS Query4
FROM		(
			SELECT	PartNo,
				Qty,
				'TableA' AS TableName
			FROM	@TableA

			UNION ALL

			SELECT	PartNo,
				Qty,
				'TableB'
			FROM	@TableB
		) AS d
GROUP BY	PartNo
ORDER BY	PartNo

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources