SQL SELECT from a SELECT in JOIN

Question

I am trying to use this answer to do something similar, but I am getting "no such column" error at the first line!

my SQL code is as follows:

SELECT product.id, T2.[tt]
FROM product
JOIN (
SELECT reviews.product_id, AVG(reviews.rating) AS [tt]
FROM reviews 
WHERE ...
GROUP BY reviews.product_id) T2
ON product.id = T2.product_id
WHERE ...

Please check your table definitions, I just tested your code on SQLite and Oracle http://sqlfiddle.com/#!7/6114b/2 and http://sqlfiddle.com/#!4/75533/2 Perhaps the problem is in your where conditions? Maybe post the whole query or use one of the sqlfiddles — Paul Maxwell, Jul 05 '14 at 00:13

score 0 · Answer 1 · answered Jul 04 '14 at 20:08

Maybe removing the square brackets and the as can help you:

select product.id, t2.tt
from 
    product
    join (
        select product_id, avg(rating) tt
        from reviews
        where -- Your where conditions for the reviews table
    ) t2 on product.id = t2.product_id
where -- More where conditions

Also, consider not using a subquery. If your query is just like this, you could so something more simple:

select product.id, avg(reviews.rating) tt
from
    product
    join reviews on product.id = reviews.product_id
where -- All the conditions you need to define

If there are rows in the product table that have no matching records in the reviews table, but you still want them to be on the result, consider using a left join:

select product.id, avg(reviews.rating) tt
from
    product
    left join reviews on product.id = reviews.product_id
where 
    reviews.product_id is null
    or reviews.product_id is not null and (
        -- All the conditions you need to define for the matching rows in the 
        -- reviews table
    )

score 0 · Answer 2 · answered Jul 05 '14 at 07:51

The query you currently show in the question (if you remove the [] ) will work in both SQLite & Oracle. So it's not displayed above or cannot be seen only by through the query code. Please check the table definitions; it may be a simple as a wrong field name.

Note you haven't displayed the conditions used in your where clause either, it might be in there.

score -1 · Answer 3 · answered Jul 04 '14 at 19:25

-1

It's happened to me before (in older versions of MySQL if not mistaken). You HAVE to give an alias to every table:

SELECT pro.id, T2.[tt]
FROM product pro
JOIN (
SELECT reviews.product_id, AVG(reviews.rating) AS [tt]
FROM reviews 
WHERE ...
GROUP BY reviews.product_id) T2
ON pro.id = T2.product_id
WHERE ...

answered Jul 04 '14 at 19:25

rion18

1,211
16
22

Not true. At least in MySQL there's no need to define aliases for every table – Barranka Jul 04 '14 at 20:01
adding aliases to subqueries is good practice even in MySQL, and Barranka is correct in some dbms's it is mandatory - but it's not the issue here obviiously – Paul Maxwell Jul 04 '14 at 23:50

SQL SELECT from a SELECT in JOIN

3 Answers3