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:
ID Name ParentID
287 Safeway 0
343 Safeway 287
526 Safeway 287
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