CaptainForge CaptainForge - 2 months ago 5
MySQL Question

How to check if an ID exists in the result of a sub-query?

Let's say I have the following query to list the average value of a house people own:

SELECT PERSON.NAME, AVG
FROM PERSON, (
SELECT HOUSE.PID AS PID, AVG(HOUSE.VALUE) as AVG
FROM HOUSE
GROUP BY PID
) HOUSES
WHERE PERSON.PID = HOUSES.PID OR PERSON.ID NOT IN (
SELECT PID
FROM HOUSE
)


The query does what I want it to do, except it doesn't include the people who don't have any houses, who should have "0" as their house cost average.

Is this possible, or am I way off?

Answer

Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax. For instance, in this case, you want a LEFT JOIN, but cannot express it.

SELECT p.NAME, AVG_VALUE
FROM PERSON p LEFT JOIN
     (SELECT PID , AVG(VALUE) as AVG_VALUE
      FROM HOUSE
      GROUP BY PID
     ) h
     ON p.PID = h.PID;

If you want 0 instead of NULL, use COALESCE(AVG_VALUE, 0) as AVG_VALUE in the outer query.

Comments