SQL exists not working

I have the SQl query below which list the stores

Whenever i get "All city types" in the citytypename fields , i 开发者_JAVA百科DO NOT need to list the other rows for the particular storelocationid

Result

71    8    0    All City Types    West    Dhanalakshmi store     All Cities    All States
71    8    1    Tier 2            West    Dhanalakshmi store     Bangalore     Karnataka
71    8    2    Tier 2            West    Dhanalakshmi store     Ahmedabad     Gujarat      

how i will rewrite my query for this?

SELECT  LL.StoreLocationID
       ,LC.[StoreID]
       ,ISNULL(LC.CityID,0) CityID
       ,ISNULL(C.CityTypeName,'All City Types') CityTypeName
       ,ISNULL(LL.RegionName,'All Regions') RegionName
       ,L.[Name] StoreName
       ,ISNULL(C.Name,'All Cities') AS CityName
       ,ISNULL(S.Name, 'All States') AS StateName
FROM [StoreCity] LC
    INNER JOIN [Store] L ON L.[StoreID] = LC.[StoreID] 
    INNER JOIN [StoreLocation] LL ON LL.StoreID=LC.StoreID
    LEFT OUTER JOIN [City] C ON LC.CityID = C.CityID
    LEFT OUTER JOIN [State] S ON C.StateID = S.StateID
WHERE StoreLocationID =  71
AND (
   /* current row is 'All City Types' */
   C.CityTypeName IS NULL
   OR 
   /* current row is not 'All City Types', 
      but there does not exist another row that is. */
   NOT EXISTS (
      SELECT 1
      FROM [StoreLocation] LL2 ON LL2
      INNER JOIN [StoreCity] LC2 ON LC2.[StoreID] = LL2.[StoreID]
      INNER JOIN [City] C2 ON C2.[CityID] = LC2.[CityID]
      WHERE LL2.StoreLocationID = LL.StoreLocationID
      AND C2.CityTypeName IS NULL
   )
)

---

I think you are trying to look for DISTINCT and possible a corresponding GROUP BY.

Something like this:

SELECT DISTINCT ISNULL(C.CityTypeName,'All City Types') CityTypeName FROM ...

---

SELECT  final .StoreLocationID
       ,final .[StoreID]
       ,final .CityID
       ,final .CityTypeName
       ,final .RegionName
       ,final .StoreName
       ,final .CityName
       ,final .StateName from ( SELECT  LL.StoreLocationID
       ,LC.[StoreID]
       ,ISNULL(LC.CityID,0) CityID
       ,ISNULL(C.CityTypeName,'All City Types') CityTypeName
       ,ISNULL(LL.RegionName,'All Regions') RegionName
       ,L.[Name] StoreName
       ,ISNULL(C.Name,'All Cities') AS CityName
       ,ISNULL(S.Name, 'All States') AS StateName FROM [StoreCity] LC
    INNER JOIN [Store] L ON L.[StoreID] = LC.[StoreID] 
    INNER JOIN [StoreLocation] LL ON LL.StoreID=LC.StoreID
    LEFT OUTER JOIN [City] C ON LC.CityID = C.CityID
    LEFT OUTER JOIN [State] S ON C.StateID = S.StateID WHERE StoreLocationID =  71 AND (    /* current row is 'All City Types' */   C.CityTypeName IS NULL    OR     /* current row is not 'All City Types', 
      but there does not exist another row that is. */    NOT EXISTS (
      SELECT 1
      FROM [StoreLocation] LL2 ON LL2
      INNER JOIN [StoreCity] LC2 ON LC2.[StoreID] = LL2.[StoreID]
      INNER JOIN [City] C2 ON C2.[CityID] = LC2.[CityID]
      WHERE LL2.StoreLocationID = LL.StoreLocationID
      AND C2.CityTypeName IS NULL    ) ) ORDER BY FIELD("All City Types", CityTypeName) DESC ) as final group by final.StoreLocationID

Try this, not tested...