CODEWITHSUNDEEP
mysqlsql
Cârnăciov
Cârnăciov

Reputation: 1144

Use a value from SELECT in WHERE

So I'm selecting a name and a number of sports that name is related to, and I need to only select them when that number of sports is greater than 1.

SELECT DISTINCT name AS FullName,  
      (SELECT COUNT(id) FROM coaches WHERE coaches.name=FullName) AS NrOfSports  
FROM coaches WHERE NrOfSports>1

If WHERE is removed the query works just fine and displays all rows of which some have only "1" as NrOfSports. When I add it to the WHERE clause I get an error because it's not recognized. This baffles me since if I were to use it in another SELECT column it would work fine.

Is there a way to do this? It can't be software dependant.

Upvotes: 4

Views: 2714

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Error explanation

You're getting an error, because WHERE clause can not access columns by their alias in the very same level. There are two solutions, which I don't recommend, but for the sake of your question I'm attaching:

(1) Move your WHERE NrOfSports > 1 to another level

SELECT *
FROM (
    SELECT DISTINCT 
        name AS FullName,  
        ( SELECT COUNT(id) FROM coaches c2 WHERE c1.name = c2.name ) AS NrOfSports  
    FROM coaches c1
    ) foo
WHERE NrOfSports > 1

(2) Use your condition in the same level.

SELECT DISTINCT
    name AS FullName,
    ( SELECT COUNT(id) FROM coaches c2 WHERE c1.name = c2.name ) AS NrOfSports 
FROM 
    coaches c1
WHERE
    ( SELECT COUNT(id) FROM coaches c2 WHERE c1.name = c2.name ) > 1

That would fix the error, but it still wouldn't be efficient (!)

Better approach

Instead, you could combine a power of GROUP BY with it's own WHERE clause called HAVING to limit your output only to those names that have more than 1 id.

SELECT DISTINCT 
  name AS FullName,
  count(id) AS NrOfSports 
FROM
  coaches 
GROUP BY
  name
HAVING 
  count(id) > 1

Upvotes: 2

Luc M
Luc M

Reputation: 17334

The answer of sgeddes is better than mine. But you could do also do this query :

    SELECT * 
    FROM (
       SELECT DISTINCT name AS FullName,  
             (SELECT COUNT(id) FROM coaches WHERE coaches.name=FullName) AS NrOfSports  
       FROM coaches  
   ) tmp  
     WHERE NrOfSports>1

Upvotes: 2

sgeddes
sgeddes

Reputation: 62861

Use Group By and Having instead:

SELECT name AS FullName, 
   COUNT(id) AS NrOfSports 
FROM coaches 
GROUP BY name
HAVING COUNT(id) > 1

Your correlated query can work, you just need to move it to a subquery and then you can add the where criteria. However, I believe the group by would be faster.

Upvotes: 5

Related Questions