right join? FUll outer 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 2005 Forums

Transact-SQL (2005)

right join? FUll outer join?

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-11-25 : 15:49:02

Hi

I got 3 tables

1 table with name category

category =
Cid | nameC

1 horror
2 comedy
3 drama

second table are films

Fid | nameF | Cid | Pid

1 movie1 1 20
2 movie2 3 21
3 movie3 2 20
4 movie4 2 23
5 movie5 1 21

third are publishers

Pid | nameP
20 john
21 bart
23 gren
26 jack

Now i want to see all the movies that have been published in every category AND i need to see the zero's, so i also need to see publisher who hasent published any film or names of categorys where the publisher did'nt bring out any films.)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-25 : 16:26:30

Then FULL OUTER JOIN is your friend I think.

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-26 : 05:40:16

Yup -- I think so too. I never have any reason to use FULL OUTER JOIN however.

Did you want output like this:


/*
I got 3 tables

1 table with name category

category =
Cid | nameC

1 horror
2 comedy
3 drama

second table are films

Fid | nameF | Cid | Pid

1 movie1 1 20
2 movie2 3 21
3 movie3 2 20
4 movie4 2 23
5 movie5 1 21


third are publishers

Pid | nameP
20 john
21 bart
23 gren 
26 jack
*/

DECLARE @category TABLE ([CID] INT, [nameC] VARCHAR(255))
DECLARE @films TABLE ([FID] INT, [nameF] VARCHAR(255), [CID] INT, [PID] INT)
DECLARE @publishers TABLE ([PID] INT, [nameP] VARCHAR(255))

INSERT @category ([CID], [nameC])
      SELECT 1, 'Horror'
UNION SELECT 2, 'Comedy'
UNION SELECT 3, 'Drama'
UNION SELECT 10, 'Romance' -- Added to test no link to this category

INSERT @films ([FID], [nameF], [CID], [PID])
      SELECT 1, 'movie1', 1, 20
UNION SELECT 2, 'movie2', 3, 21
UNION SELECT 3, 'movie3', 2, 20
UNION SELECT 4, 'movie4', 2, 23
UNION SELECT 5, 'movie5', 1, 21

INSERT @publishers ([PID], [nameP])
      SELECT 20, 'John'
UNION SELECT 21, 'Bart'
UNION SELECT 23, 'Gren'
UNION SELECT 26, 'Jack'

-- FULL OUTER JOIN
SELECT
	c.[nameC] AS [Category]
	, f.[nameF] AS [Film]
	, p.[nameP] AS [Publisher]
FROM
	@category c
	LEFT JOIN @films f ON f.[CID] = c.[CID]
	FULL OUTER JOIN @publishers p ON p.[PID] = f.[PID]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources