0

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
CaptainForge
  • 1,365
  • 7
  • 21
  • 46
  • 1
    Your query is basically an INNER join with another subquery, you need a left JOIN – Mihai Sep 17 '16 at 17:12
  • Mihai's nailed it. But if you're still struggling, see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Sep 17 '16 at 17:13
  • Possible duplicate of [MySQL: Quick breakdown of the types of joins](http://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins) – Drew Sep 17 '16 at 17:15

2 Answers2

2

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.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You should use left join, that way records that appear in persons table and dont have corresponding records in houses table will grt nulls on the columns from houses

Nir Levy
  • 12,750
  • 3
  • 21
  • 38