Frustrated Join - 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 2008 Forums

Transact-SQL (2008)

Frustrated Join

Author

Topic

rico1931
Starting Member

37 Posts

Posted - 2009-06-09 : 15:32:31

Hello everyone i'm trying to get this join to stop doing duplicates and its driving me nuts. Here is some sampel data with some fields taken out because its a long join


FACT_ID	FACT_KEY	FACT_CONTENT	FACT_BKGD_ALIAS	FACT_GRA_ALIAS	GRA_ALIAS	GRA_SITEDIR
975	100	<b>Innovation Through Dedication</b>	gradient1.gif	Bald_Eagle___US_Flag_(100pct_Gauss).jpg	Bald_Eagle___US_Flag_(100pct_Gauss).jpg	/SiteImages/F99/
975	100	<b>Innovation Through Dedication</b>	gradient1.gif	Bald_Eagle___US_Flag_(100pct_Gauss).jpg	gradient1.gif	/SiteImages/F99/
1083	100	<b>What's New<br />	NULL	Null	NULL	NULL
1010	100		NULL	SV2C-1000-1111ZZZA	SV2C-1000-1111ZZZA	/SiteImages/F01/
1000	100		NULL	SV3C-1500A-1111ZADZ.png	SV3C-1500A-1111ZADZ.png	/SiteImages/F01/

Next here is the code

SELECT DISTINCT * FROM dbo.FACTS LEFT OUTER JOIN dbo.GRAPHICS ON dbo.FACTS.FACT_GRA_ALIAS = dbo.GRAPHICS.GRA_ALIAS 
OR dbo.FACTS.FACT_BKGD_ALIAS = GRA_ALIAS 
LEFT OUTER JOIN dbo.STYLES ON dbo.FACTS.FACT_STYLE_ID = dbo.STYLES.StyleID  
WHERE dbo.FACTS.FACT_KEY = '100' AND dbo.FACTS.FACT_REGION = 'LEFT' 
ORDER BY FACTS.FACT_SEQ_NUM"

Don't mind the join on STYLES that part is solid the problem is that on GRAPHICS it give me a double record because the First LEFT OUTER JOIN on the correct GRA_ALIAS in the GRAPHICS table and the second one is not returning only a single record but double. I hope this makes some sense.

Thanks again

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-09 : 15:46:23

Have you tried an INNER JOIN on GRAPHICS instead?

Some days you're the dog, and some days you're the fire hydrant.

rico1931
Starting Member

37 Posts

Posted - 2009-06-09 : 16:33:15

Ya and nothing I still get the duplicates

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-09 : 16:54:01

Then the problem is that [graphics] has (potentially) multiple rows for a given row in [facts]. Does [graphics] have any unique constraints, primary key, or at least a logical key that will determine what must be unique in each row?

Take just one row (like fact_id=975) and select from [graphics] for that row. ie: GRA_ALIAS = the [gra_alias] value from 975 row in [facts].

That should show you why you're getting the dupes. solving your dupes will depend on what column(s) you need out of your [graphics] table.

Be One with the Optimizer
TG

rico1931
Starting Member

37 Posts

Posted - 2009-06-10 : 09:45:37

Thanks everyone for your help. I just changed the content in the field to no potentially join more then once.. a little cheat but these people want results and I want a pay check lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources