SELECT cols, (SELECT inner query) AS innercol FROM table WHERE innercol something

Question

It works as long as I avoid the WHERE stream_json ... can this be fixed or does it require JOINS?

SELECT id, stuff,
    (SELECT count(*) 
     FROM inner_table
     WHERE inner_table.movie_id = outer_table.id) 
          AS stream_json
FROM outer_table 
WHERE stream_json != 0

What exactly your question is ? – M Khalid Junaid Oct 02 '13 at 10:01 — M Khalid Junaid, Oct 02 '13 at 10:01
@dianuj can this be fixed or does it require JOINS? – Ruben Serrate Oct 02 '13 at 10:01 — Ruben Serrate, Oct 02 '13 at 10:01

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

You can use HAVING to access the custom aliases they cannot be entertained using WHERE clause

SELECT (SELECT count(*) FROM inner_table
WHERE inner_table.movie_id = outer_table.id) AS stream_json
FROM outer_table HAVING stream_json != 0

See this for reference

edited May 23 '17 at 12:29

Community

1
1

answered Oct 02 '13 at 10:02

M Khalid Junaid

63,861
10
90
118

score 1 · Answer 2 · answered Oct 02 '13 at 10:03

You cannot use aliases in WHERE conditions. However, you can use it in HAVING conditions. Another choice is to repeat whole subquery but that looks ugly. I recommend using HAVING.

SELECT (SELECT count(*) FROM inner_table
WHERE inner_table.movie_id = outer_table.id) AS stream_json
FROM outer_table HAVING stream_json != 0

score 0 · Answer 3 · answered Oct 02 '13 at 10:15

As above you can use HAVING, or you can join against a subselect which avoid the correlated subquery

SELECT outer_table.id, outer_table.stuff, stream_json
FROM outer_table 
INNER JOIN
(
    SELECT movie_id, count(*) AS stream_json
    FROM inner_table
    GROUP BY movie_id
) Sub1
ON Sub1.movie_id = outer_table.id

Note that you do not need the check for stream_json != 0 for this as it is implicit in the INNER JOIN

score 0 · Answer 4 · answered Oct 02 '13 at 10:26

You can't access the column alias on the same level. You need to wrap that into a derived table:

select *
from (
  SELECT id, 
         stuff,
         (SELECT count(*) 
          FROM inner_table
          WHERE inner_table.movie_id = outer_table.id) AS stream_json
  FROM outer_table
) t  
WHERE stream_json <> 0;

But Kickstart's answer is probably the better solution to this problem.

SELECT cols, (SELECT inner query) AS innercol FROM table WHERE innercol something

4 Answers4