Question 113154 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Filtering using WHERE for a subquery

i consider myself a pretty well-versed developer, but this one has me stumped.

The actual use case is somewhat more complicated than this (i have built a data-view framework that allows you to filter and search data), but at its simplest…

Why can’t I do something like this?:

SELECT
    fundraisers.id,
    (
        SELECT
            count(*)
        FROM
            transactions
        WHERE
            transactions.fundraiser_id = fundraisers.id) AS total
    FROM
        fundraisers
    WHERE
        total > 331
    ORDER BY
        total DESC

I’ve also tried:

  • I’m aware i can successfully use HAVING to do this, but i need it to be part of the WHERE clause in order to be able to use it in conjunction with other filters using the right AND/OR conditions.
  • doing it as a subquery JOIN instead, but it never seems to return the right count of transactions for the row.

Any help is appreciated! Thanks folks.

Tags:

2

Answers


  1. 0

    i don’t know what do you want to select but try this

    select fundraisers.id,count(*) as total FROM
        fundraisers f join transactions t on t.fundraiser_id=f.fundraiser_id
    WHERE
        total > 331
    ORDER BY
        total DESC
    Login or Signup to reply.
  2. 0

    You can use a derived table, in other words a subquery in the FROM clause instead of the select-list.

    SELECT t.fundraiser_id, t.total
FROM
    fundraisers AS f
JOIN (
    SELECT fundraiser_id, COUNT(*) AS total
    FROM transactions
    GROUP BY fundraiser_id
) AS t ON t.fundraiser_id = f.id
WHERE
    t.total > 331
ORDER BY
    t.total DESC;

    The reason you can’t refer to an alias in the WHERE clause is that the conditions of the WHERE clause is evaluated before expressions in the select-list are evaluated. This is a good thing, because you wouldn’t want the select-list to be evaluated for potentially millions of rows that would be filtered out by the conditions anyway. Evaluating the select-list only for rows that are included in the result helps improve performance.

    But it means the result of those expressions in the select-list, and their alias, isn’t available to be referenced by conditions in the WHERE clause.

    The workaround I show in my example above produces the results in a subquery, which happens before the WHERE clause gets to filter the results.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search