CODEWITHSUNDEEP
sqlsqliteequality
user209306
user209306

Reputation: 89

How to exclude a specific value in my sql query?

I have the following query which outputs everything correctly except that I don't want one of the rows it's giving me:

SELECT r.Region_Cd, r.Region_Desc, 
COUNT(a.Region) AS Count
FROM Region r
--WHERE r.Region_Desc <> "Unspecified"
LEFT JOIN HU_UNIT_STATE_LEVEL a ON r.Region_Cd = a.Region
GROUP BY r.Region_Cd, r.Region_Desc;

I would like to exclude the entries where r.Region_Desc is "Unspecified" but I'm unsure about where to put the clause. I commented out the line that I thought would do the trick but ended up giving me an error. Could I also get an explanation as to why my current query doesn't make sense?

Upvotes: 0

Views: 1025

Answers (3)

Amit Sharma
Amit Sharma

Reputation: 1212

By taking performance into consideration you should use something like this.

SELECT r.Region_Cd, r.Region_Desc, 
COUNT(a.Region) AS Count
FROM Region r
LEFT JOIN HU_UNIT_STATE_LEVEL a ON r.Region_Cd = a.Region and r.Region_Desc <> "Unspecified"
GROUP BY r.Region_Cd, r.Region_Desc;

Upvotes: 0

swetha
swetha

Reputation: 130

We cannot write a where condition for parent table from which we are trying to join the other tables. But we can write a condition with other joining tables. so use condition after join.

SELECT r.Region_Cd, r.Region_Desc, 
COUNT(a.Region) AS Count
FROM Region r
LEFT JOIN HU_UNIT_STATE_LEVEL a ON r.Region_Cd = a.Region
WHERE r.Region_Desc <> 'Unspecified'
GROUP BY r.Region_Cd, r.Region_Desc

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

Use where condition after join.

SELECT r.Region_Cd, r.Region_Desc, 
COUNT(a.Region) AS Count
FROM Region r
LEFT JOIN HU_UNIT_STATE_LEVEL a ON r.Region_Cd = a.Region
WHERE r.Region_Desc <> 'Unspecified'
GROUP BY r.Region_Cd, r.Region_Desc

Upvotes: 1

Related Questions