Outer Join on same table? SQL SERVER 2005 2

[maxwell2323]

USING TSQL on SSMS 2005
I have a table with entries in two columns: Location, Widget. I want to get a second table that has rows of all locations with all widgets. Or I could settle for a table (with the two columns) that has Locations where the rows for the locations have a widget where the location/widget is not in the original table. I would have to write code where nothing can be hard coded, as the number of Locations and/or widgets are dynamic. I apologize if I have to post this twice - not sure if an attachment is better. Remember - even though you see 3 distinct locations and 4 distinct widgets, these distinct numbers can vary. How do I code for this? I am trying to do an outer join on the same table but it is not working. Thanks so much in advance.

Original Table

Location| Widget

Chicago | P
Carmel | A
Carmel | B
Indy | A
Indy | B
Indy | C


Table I am wanting:

Location| Widget

Chicago | A
Chicago | B
Chicago | C
Carmel | C
Carmel | P
Indy | P

 

[gmmastros]

I think this is what you want.

Declare @Temp Table(Location VarChar(100), Widget VarChar(100))

Insert Into @Temp Values('Chicago', 'P')
Insert Into @Temp Values('Carmel', 'A')
Insert Into @Temp Values('Carmel', 'B')
Insert Into @Temp Values('Indy', 'A')
Insert Into @Temp Values('Indy', 'B')
Insert Into @Temp Values('Indy', 'C')

; With Locations As
(
	Select	Distinct Location
	From    @Temp
), Widgets As
(
	Select  Distinct Widget
	From    @Temp
)
Select Locations.Location,
       Widgets.Widget
From   Locations
       Cross Join Widgets
	   Left Join @Temp T
	     On Locations.Location = T.Location
		 And Widgets.Widget = T.Widget
Where  T.Location Is NULL
Order By Locations.Location,
       Widgets.Widget

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[maxwell2323]

Oh my God this is exactly what I was looking for. Thanks so much gmmastros !!!!!!!!!!!!!!

 

[gmmastros]

Do you understand it all? Is there anything you would like for me to explain?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom