elemke
asked on
SQL Server 2005 Distinct On One Column
I need to write a query to display distinct values. Here is the scenario:
I have a Store table and a StoreSpecials table. In the Store table I have the following columns StoreID, StoreName, ParentID. In the StoreSpecials table I have the following columns StoreID and SpecialID. The ParentID equals 0 if the store is a prent and equals the ID StoreID of the parent for child stores. In the StoreSpecials table there can be many SpecialIDs for a group of stores. here is some sample data:
Store table
ID Name ParentID
287 Safeway 0
343 Safeway 287
526 Safeway 287
StoreSpecials table
StoreID SpecialID
287 1
287 2
343 1
343 2
343 3
526 1
526 2
526 4
I need a query that displays the distinct SpecialIDs where the distinct SpecialIDs display for the parent instead of the children in the case where the same SpecialID is spread across multiple StoreIDs.
Thanks, Erich Lemke
I have a Store table and a StoreSpecials table. In the Store table I have the following columns StoreID, StoreName, ParentID. In the StoreSpecials table I have the following columns StoreID and SpecialID. The ParentID equals 0 if the store is a prent and equals the ID StoreID of the parent for child stores. In the StoreSpecials table there can be many SpecialIDs for a group of stores. here is some sample data:
Store table
ID Name ParentID
287 Safeway 0
343 Safeway 287
526 Safeway 287
StoreSpecials table
StoreID SpecialID
287 1
287 2
343 1
343 2
343 3
526 1
526 2
526 4
I need a query that displays the distinct SpecialIDs where the distinct SpecialIDs display for the parent instead of the children in the case where the same SpecialID is spread across multiple StoreIDs.
Thanks, Erich Lemke
can you show a sample of your expected output?
Try This
SELECT distinct Id,SpecialID
FROM Store S INNER JOIN StoreSpecials SS
ON S.Id = SS.StoreId
WHERE S.ParentId = 0
SELECT distinct Id,SpecialID
FROM Store S INNER JOIN StoreSpecials SS
ON S.Id = SS.StoreId
WHERE S.ParentId = 0
ASKER
Based on the sample data above the expected out would be:
StoreID SpecialID ParentID
287 1 0
287 2 0
343 3 287
526 4 287
StoreID SpecialID ParentID
287 1 0
287 2 0
343 3 287
526 4 287
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For an explanation of the above, please read this article: Analytical SQL : Where do you rank?