sql error using subquery in select and from clause

Question

I get error When I use subquery in select and from clause
Error: https://www.awesomescreenshot.com/image/3069216/0505cad495528e9f9af2281ea281415c.

Here what I have done so far:

SELECT m.id, m.title_name, m.release_date, m.plot, m.poster, 
  (SELECT count(*) FROM users_ratings WHERE type_id = m.id AND type_name = 'movies' LIMIT 1) count_user_rating 
FROM (SELECT u.* FROM users u
      LEFT JOIN users_ratings ur ON ur.user_id = u.id
      WHERE ur.type_id = 2
      AND ur.type_name = 'movies') u
LEFT JOIN users_ratings ur ON ur.user_id = u.id
LEFT JOIN movies m ON m.id = ur.type_id
WHERE ur.type_name = 'movies'

UNION

SELECT s.id, s.title_name, s.first_air_date release_date, s.plot, s.poster, 
  (SELECT count(*) FROM users_ratings WHERE type_id = s.id AND type_name = 'series' LIMIT 1) count_user_rating 
FROM (SELECT u.* FROM users u
      LEFT JOIN users_ratings ur ON ur.user_id = u.id
      WHERE ur.type_id = 2
      AND ur.type_name = 'movies') u
LEFT JOIN users_ratings ur ON ur.user_id = u.id
LEFT JOIN series s ON s.id = ur.type_id
WHERE ur.type_name = 'series'

That's because the subquery count_user_rating is not fitting well with the syntax. It is begin evaluated as a table and you seem to want to use it as a column. — Vash
– Vash, Commented Dec 30, 2017 at 2:31
Since it is going to evaluate as one value from the COUNT(*) function, although in the form of a single-row table, are you trying to populate that column with same value across all the rows? — Vash
– Vash, Commented Dec 30, 2017 at 2:34
@Vashi - Count will not be same for all the records. Check there is a condition in where clause. Count will be same for each id — Pரதீப்
– Pரதீப், Commented Dec 30, 2017 at 2:45

Pரதீப் · Accepted Answer · 2017-12-30 02:43:24Z

1

Not sure whats wrong in your query, seems fine to me. But here is another way to achieve it. Try this

SELECT m.id,
       m.title_name,
       m.release_date,
       m.plot,
       m.poster,
       t.count_user_rating
FROM   (SELECT u.*
        FROM   users u
               LEFT JOIN users_ratings ur
                      ON ur.user_id = u.id
        WHERE  ur.type_id = 2
               AND ur.type_name = 'movies') u
       LEFT JOIN users_ratings ur
              ON ur.user_id = u.id
       LEFT JOIN movies m
              ON m.id = ur.type_id
       LEFT JOIN (SELECT type_id,
                         Count(*) AS count_user_rating
                  FROM   users_ratings
                  WHERE  NAME = 'movies'
                  GROUP  BY type_id) t
              ON t.type_id = m.id
WHERE  ur.type_name = 'movies'
UNION
SELECT s.id,
       s.title_name,
       s.first_air_date release_date,
       s.plot,
       s.poster,
       t.count_user_rating
FROM   (SELECT u.*
        FROM   users u
               LEFT JOIN users_ratings ur
                      ON ur.user_id = u.id
        WHERE  ur.type_id = 2
               AND ur.type_name = 'movies') u
       LEFT JOIN users_ratings ur
              ON ur.user_id = u.id
       LEFT JOIN series s
              ON s.id = ur.type_id
       LEFT JOIN (SELECT type_id,
                         Count(*) AS count_user_rating
                  FROM   users_ratings
                  WHERE  NAME = 'series'
                  GROUP  BY type_id) t
              ON t.type_id = s.id
WHERE  ur.type_name = 'series'

answered Dec 30, 2017 at 2:43

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vash Over a year ago

This is what I was thinking. Should be fine.

Fahardine Ben Fahardine Over a year ago

@Pரதீப் I tried your solution and it's working like charme

Collectives™ on Stack Overflow

sql error using subquery in select and from clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related