sql - 这是具有联接的有效相关子查询吗？(Is this a valid correlated sub query with joins?)

Question

So I have a table Car(Car_id, Value, Make) , another table Person(Person_id, Name) and a third table linking the two together PerCar(Car_id, Person_id) .

(所以我有一个表Car(Car_id, Value, Make) ，另一个表Person(Person_id, Name)和第三个表，将两者链接在一起PerCar(Car_id, Person_id) 。)

So I have this correlated subquery:

(所以我有这个相关的子查询：)

SELECT MAKE, VALUE 
FROM CAR WHERE VALUE > (SELECT AVG(VALUE) FROM CAR C WHERE C.CAR_ID = CAR_ID);

So if I wanted to display the person's name also, is this also valid correlated sub query:

(因此，如果我也想显示此人的姓名，这是否也是有效的相关子查询：)

SELECT Car.Make, Car.Value, Person.Name 
FROM PerCar NATURAL JOIN Car 
NATURAL JOIN Person where Car.Value > (SELECT AVG(VALUE) FROM Car);

  ask by Drakus translate from so

Answer 1

Your query should be as below-

(您的查询应如下-)

You can check DEMO HERE

(您可以在此处检查演示)

SELECT Car.Make, 
Car.Value, 
Person.Name 
from Car
INNER JOIN CarPer 
    ON Car.Car_id = CarPer.Car_id 
INNER JOIN Person
    ON Person.Person_id = CarPer.Person_ID        
WHERE Car.Value > (SELECT AVG(VALUE) FROM Car);

Remember, here joining can be INNER, OUTER, LEFT or other joins based on your requirement and data structure.

(请记住，根据您的要求和数据结构，这里的连接可以是INNER，OUTER，LEFT或其他连接。)