Query to bring back one column instead of three

[MarkXaml]

I have a query inside of a stored procedure that will retrieve the information that I need, but it is in 3 separate columns. What I ultimately need is one column of distinct codes. Can someone point me in the best direction to do this?

Here's the query:

SELECT		ps.StoreCode
			,ps2.StoreCode
			,ps3.StoreCode
FROM		Stores ps (NOLOCK)
LEFT OUTER JOIN	Stores ps2 (NOLOCK)
ON			ps.StoreID = ps2.ParentStoreID
LEFT OUTER JOIN	Stores ps3 (NOLOCK)
ON			ps2.StoreID = ps3.ParentStoreID
LEFT OUTER JOIN	Users hr (NOLOCK)
ON			ps.StoreID=hr.StoreID
WHERE		hr.UserName='XYZ'

This will return a recordset similar to:

districtcode regioncode1 storecode1
districtcode regioncode1 storecode2
districtcode regioncode1 storecode3
districtcode regioncode2 storecode4
districtcode regioncode2 storecode5
districtcode regioncode3 storecode6
...and so on

each user has a storecode (or district or region dependant on the user). That store may or may not be a parent store of one or more stores. The user always has the highest level of access, so I need that code and every code under it.

Using the example above, I am trying to get:
districtcode
regioncode1
regioncode2
regioncode3
storecode1
storecode2
storecode3
storecode4
storecode5
storecode6

I'm sure this is probably something very simple... I just need a push. Any help would be much appreciated.

--Mark

 

[gmmastros]

>> I just need a push

I suggest you use [!]Union[/!].

SQL Server also has [!]Union All[/!] which will allow duplicates. Union will not.

If you have further questions, ask away.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkXaml]

Thanks, George... I've got it now. I am familiar with unions, I was just curious if there was a more creative JOIN I could have done that would have brought back the results I was looking for. I'll just stick with the unions and move on.

Thank you!

 

[ESquared]

JOIN inherently creates more columns across the result set. Additional row creation is a function of the logic of the join where one row in the parent table logically represents multiple rows from the child table, thus the "extra" rows aren't really extra.

UNION inherently creates more rows down the result set. No new columns are created.

If you're using SQL Server 2005 or 2008, a CTE would perfectly answer your requirements, adding just the child values at each stage until there are no more.

In SQL Server 2000, you'd have to do it in a loop.

In both cases you need a depth column (either from the original tables or created in your CTE/loop) to avoid joining to the parent nodes already traversed.

There's something you could do with your above query and a CROSS JOIN to a table with three rows, but it's not really the best way to satisfy this. You could also make your above query a CTE or stash it in a temp table and UNION select from it 3 times.