Get same info using 2 joins - 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)

Get same info using 2 joins

Author

Topic

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 09:45:13

Hello there folks. I'm trying to use two joins to get information from a related table but its only returning the first join and not the next. Here is the SQL

LEFT OUTER JOIN dbo.GRAPHICS ON dbo.FACTS.FACT_GRA_ALIAS = dbo.GRAPHICS.GRA_ALIAS 
LEFT OUTER JOIN dbo.GRAPHICS a  ON dbo.FACTS.FACT_BKGD_ALIAS = dbo.GRAPHICS.GRA_ALIAS

The first LEFT OUTER JOIN gets the correct info but then on the second it does not join GRAPHIC.GRA_ALIAS at all. Any help would be greatly appreciated.

THANKS!

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 09:53:09

I tried to put an OR in there but that didn't work

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-02 : 10:17:07

Your second join is referenced as a, not dbo.GRAPHICS, so put
put a.GRA_ALIAS in the ON clause.

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 10:19:44

When I do that it tell me that a.dbo.GRAPHICS is in Invalid object name

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 10:39:09

Well This works

LEFT OUTER JOIN dbo.GRAPHICS as graphics1 ON dbo.FACTS.FACT_GRA_ALIAS = graphics1.GRA_ALIAS
LEFT OUTER JOIN dbo.GRAPHICS ON dbo.FACTS.FACT_BKGD_ALIAS = dbo.GRAPHICS.GRA_ALIAS

But it returns the information double, Meaning goes through the first join then going through the second join and just puts it further down the result table. I need them to be in the same area as each other to be called in my website. How can I distinguish the table to have two separate names without making a whole another table

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 11:03:02

Is there anyway to change the header in one of the Joins so I can distinguish them? I'm so close yet so far

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-02 : 12:17:38

It's time to post some sample data and the required output.

I think you are going to have to pivot the data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:20:06

see this for how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 12:40:35

Here is the Sample Data in Table FACTS


FACT_BKGD_ALIAS	FACT_GRA_ALIAS
dm-top-1200.jpg gradient1.gif	
NULL	         Null
NULL	         Null
NULL	         Null
NULL	         SV2C-1000-1111ZZZA
NULL	         Null
NULL	         SV3C-1500A-1111ZADZ.png
NULL	         dm-bot-1200.jpg

Next here is the data from GRAPHICS


GRA_ALIAS
dm-top-1200.jpg
NULL
NULL
NULL
NULL
SV2C-1000-1111ZZZA
NULL
SV3C-1500A-1111ZADZ.png
dm-bot-1200.jpg

As you can see above the second record has NULL where it should have
gradient1.gif there. Instead the Code I posted above does not join it there it does a seperate join with teh same headers further down the result grid shown here


GRA_ALIAS
NULL
gradient1.gif
NULL
NULL
NULL
NULL
NULL
NULL
NULL

What I want is the following when I do a Join on GRAPHICS


GRA_ALIAS
dm-top-1200.jpg
gradient1.gif
NULL
NULL
NULL
SV2C-1000-1111ZZZA
NULL
SV3C-1500A-1111ZADZ.png
dm-bot-1200.jpg

Thanks for your help guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:56:58

your output is still not clear are you trying to merge values from both columns of table1 or are you trying to join table1 & table2?

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 13:14:43

Join table 1 and table 2 on GRA_ALIAS from FACT_BKGD_ALIAS AND FACT_GRA_ALIAS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:20:34

how did secong NULL became gradient1.gif in output?

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 13:50:05

That is exactly what I was trying to figure out and how all the rest become NULL... It has something to do with the LEFT OUTER JOINS. It pretty much Joined the two tables with this LEFT OUTER JOIN dbo.GRAPHICS as graphics1 ON dbo.FACTS.FACT_GRA_ALIAS = graphics1.GRA_ALIAS and then spit out


GRA_ALIAS
dm-top-1200.jpg
NULL
NULL
NULL
NULL
SV2C-1000-1111ZZZA
NULL
SV3C-1500A-1111ZADZ.png
dm-bot-1200.jpg

Then the next line of code LEFT OUTER JOIN dbo.GRAPHICS ON dbo.FACTS.FACT_BKGD_ALIAS = dbo.GRAPHICS.GRA_ALIAS made another join to the previous LEFT OUTER JOIN and spit out this


GRA_ALIAS
NULL
gradient1.gif
NULL
NULL
NULL
NULL
NULL
NULL
NULL

rico1931
Starting Member

37 Posts

Posted - 2009-06-02 : 13:51:39

The code above did a double join of GRA_ALLIAS column basically

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-02 : 15:24:30

Can you give us your business rules? I still don't understand how you want to join to Graphics and get those results. But, here is one way to get the results you want for your sample data:

DECLARE @Facts TABLE (FACT_BKGD_ALIAS VARCHAR(100), FACT_GRA_ALIAS VARCHAR(100))
INSERT @Facts
SELECT 'dm-top-1200.jpg', 'gradient1.gif'	
UNION ALL SELECT NULL,	         Null
UNION ALL SELECT NULL,	         Null
UNION ALL SELECT NULL,	         Null
UNION ALL SELECT NULL,	         'SV2C-1000-1111ZZZA'
UNION ALL SELECT NULL,	         Null
UNION ALL SELECT NULL,	         'SV3C-1500A-1111ZADZ.png'
UNION ALL SELECT NULL,	         'dm-bot-1200.jpg'

DECLARE @Graphics TABLE (GRA_ALIAS varchar(100))
INSERT @Graphics
SELECT 'dm-top-1200.jpg'
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT NULL
UNION ALL SELECT 'SV2C-1000-1111ZZZA'
UNION ALL SELECT NULL
UNION ALL SELECT 'SV3C-1500A-1111ZADZ.png'
UNION ALL SELECT 'dm-bot-1200.jpg'


SELECT 
	FACT_BKGD_ALIAS
FROM
	@Facts
WHERE 
	FACT_BKGD_ALIAS IS NOT NULL

UNION ALL

SELECT
	FACT_GRA_ALIAS
FROM
	@Facts

rico1931
Starting Member

37 Posts

Posted - 2009-06-03 : 08:37:48

Sorry i'm horrible at explaining these things and coping the results and modifying it so it doesn't look so long. By the way visakh16 the instructions you provided did not help me with SQL 2008. I finally was able to get the results I wanted with the following SQL


LEFT OUTER JOIN dbo.GRAPHICS ON dbo.FACTS.FACT_GRA_ALIAS = GRA_ALIAS 
OR ON dbo.FACTS.FACT_BKGD_ALIAS = GRA_SOURCEDIR

LEFT OUTER JOIN 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources