0

the question is 'List the pids of all properties that do not have the metertype “advanced” installed. Use a sub-query in your answer.

METERTYPE PROPERTYMETER

This is the query i came up with

SELECT PROPERTYMETER.pid, METERTYPE.description
 FROM PROPERTYMETER , METERTYPE
 WHERE PROPERTYMETER.metertype = METERTYPE.metertype
  AND description IN (SELECT description FROM METERTYPE WHERE description = 'conventional')

This is the result i got

enter image description here

The result is all good aside from the fact that pid 9 has both conventional and advanced metertype so i didnt really answer the question exactly right. What can i do to my query to make sure that the pid returned ONLY have metertype conventional?

Community
  • 1
  • 1
kr1s
  • 27
  • 5

1 Answers1

0

Use a subquery that gets the pids of all the properties that have metertype = 'advanced'. Then use NOT IN to exclude them from the final result.

SELECT DISTINCT pid
FROM propertymeter
WHERE pid NOT IN (
    SELECT pid
    FROM propertymeter AS p
    JOIN metertype AS m ON p.metertype = m.metertype
    WHERE m.description = 'advanced')
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • This also worked perfectly, is there any reason to use joins rather than multiple subqueries? i thin i read somewhere that joins offer better performance or are more efficient or something – kr1s Oct 13 '17 at 02:03
  • Yes, MySQL doesn't always optimize `WHERE IN (subquery)` very well. The above `NOT IN` could also be written as a `LEFT JOIN/NULL` pattern, see https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 – Barmar Oct 13 '17 at 15:44