PeteEngineer
asked on
SQL rewrite
I have the SQl query below which list the stores
Whenever i get "All city types" in the citytypename fields , i 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
You can try a NOT EXISTS. Let's start with an OR in single query. Other option might be to use UNION where you select just the 'all city types' rows and then join in those other cities where there does not exist an 'all city types' row for that store location ID.
Let me know how this works:
Let me know how this works:
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
)
)
ASKER
:OnALearning…:
I am getting this error
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHEN'.
I am getting this error
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHEN'.
ASKER
mwvisa1: I am getting the following error :
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'ON'.
ASKER
I have remove the ON statement,so the query is working .....
but there is no change in the query results i posted above
but there is no change in the query results i posted above
Sorry, I started to type this with [City] table first, then thought it may not be needed and in my shifting around I did not get rid of an ON clause. Just delete it.
i.e., run like this:
i.e., run like 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
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
)
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How about the code attached
HTH,
Mark.
Open in new window