Doubling During Discovery - 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)

Doubling During Discovery

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2008-09-09 : 11:45:28

Here's the STATIC table

Reference Location Product
200413130 D zzz
200413130 D aaa
200413130 A zzz
200413130 A aaa
200519800 F aaa
200519800 F zzz
200519800 E zzz
200519800 E aaa

Here's the Dynamic table

200413130 A
200413130 A
200519800 F
200413130 A
200413130 A
200519800 F
200519800 F
200413130 A
200413130 K

I only want to match Dynamics aginst the "zzz" Products in STATIC.
At the same time, I want to identify NON-Matches (like the new "K" entry in Dynamics. However, no matter what I do, I endup with a doubling of data:

SELECT Dynamic.[Reference], Count(Dynamic.[Reference]) AS [CountOfReference], [Static].[Location]
FROM [Static] LEFT JOIN Dynamic ON [Static].[Reference] = Dynamic.[Reference]
GROUP BY Dynamic.[Reference], [Static].[Location];

Result:
Loc CountOfLocation Reference Product
A 10 200413130 zzz
F 6 200519800 zzz
K 2 200413130 zzz

My query found the new "K" location record using the LEFT JOIN, but it tells me there are 2, when there's only one. It's doubling even the actual matched count. How can I get this result instead?

Loc CountOfLocation Reference Product
A 5 200413130 zzz
F 3 200519800 zzz
K 1 200413130 zzz

Thanks for your efforts!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 12:24:27

[code]DECLARE @Static TABLE
(
Reference INT,
Location CHAR(1),
Product CHAR(3)
)

INSERT @Static
SELECT 200413130, 'D', 'zzz' UNION ALL
SELECT 200413130, 'D', 'aaa' UNION ALL
SELECT 200413130, 'A', 'zzz' UNION ALL
SELECT 200413130, 'A', 'aaa' UNION ALL
SELECT 200519800, 'F', 'aaa' UNION ALL
SELECT 200519800, 'F', 'zzz' UNION ALL
SELECT 200519800, 'E', 'zzz' UNION ALL
SELECT 200519800, 'E', 'aaa'

DECLARE @Dynamic TABLE
(
Reference INT,
Location CHAR(1)
)

INSERT @Dynamic
SELECT 200413130, 'A' UNION ALL
SELECT 200413130, 'A' UNION ALL
SELECT 200519800, 'F' UNION ALL
SELECT 200413130, 'A' UNION ALL
SELECT 200413130, 'A' UNION ALL
SELECT 200519800, 'F' UNION ALL
SELECT 200519800, 'F' UNION ALL
SELECT 200413130, 'A' UNION ALL
SELECT 200413130, 'K'

DECLARE @Product CHAR(3)
SET @Product = 'zzz'

SELECT d.Location,
COUNT(*) AS CountOfLocation,
d.Reference,
COALESCE(s.Product, @Product) AS Product
FROM @Dynamic AS d
LEFT JOIN @Static AS s ON s.Reference = d.Reference
AND s.Location = d.Location
AND s.Product = @Product
GROUP BY d.Location,
d.Reference,
s.Product[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 15:43:04

Was the suggestion helpful?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 01:24:33

[code]SELECT d.Reference,d.Location,
s.Product,
CASE WHEN s.Product IS NULL THEN 'Non-Match' ELSE 'Match' END
FROM (SELECT DISTINCT Reference,Location FROM Dynamic) d
LEFT JOIN STATIC s
ON s.Reference=d.Reference
AND s.Location=d.Location
AND Product='zzz'[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources