2

I already have a working solution to the following problem, but I worry that it is silly or inefficient.

There is a Thing table with columns (id, attributes...) and also a ThingVersion table with columns (id, thing_id, version_attributes...) The problem is to select from Thing where exactly one corresponding ThingVersion row exists.

At the moment I have something like

SELECT Thing.id AS id, Foo.whatever
FROM Thing JOIN Foo ON Thing.id=Foo.thing_id
WHERE Thing.id IN (
    SELECT thing_id FROM ThingVersion TV
    WHERE 1 = (
        SELECT COUNT(*)
        FROM ThingVersion TV2
        WHERE TV2.thing_id = TV.thing_id)
    )
ORDER BY Foo.whatever

It seems to give the correct results, and as an emphatic non-guru it seems self-explanatory, but -- three selects?!?! -- I can't help but feel there must be a better way.

Is there?

spraff
  • 32,570
  • 22
  • 121
  • 229

3 Answers3

2

You can use a HAVING clause in your subquery:

SELECT Thing.id AS id, Foo.whatever
  FROM Thing JOIN Foo ON Thing.id=Foo.thing_id
 WHERE Thing.id IN
        ( SELECT thing_id
            FROM ThingVersion TV
           GROUP BY thing_id
          HAVING COUNT(*) = 1
        )
 ORDER BY Foo.whatever
;

You can also eliminate the JOIN in the main query:

SELECT thing_id AS id, whatever
  FROM Foo
 WHERE thing_id IN
        ( SELECT thing_id
            FROM ThingVersion TV
           GROUP BY thing_id
          HAVING COUNT(*) = 1
        )
 ORDER BY whatever
;

(I'm assuming that every value that appears in both Foo.thing_id and ThingVersion.thing_id must certainly appear Thing.id.)

ruakh
  • 175,680
  • 26
  • 273
  • 307
1
SELECT Thing.id AS id, Foo.whatever
FROM Thing JOIN Foo ON Thing.id=Foo.thing_id
where Thing.idin (select thing_id from ThingVersion group by thing_id having COUNT(*)>1)
Diego
  • 34,802
  • 21
  • 91
  • 134
1

Solution using strictly JOINs:

SELECT t.*
FROM Thing t
JOIN ThingVersion tv1
  ON tv1.thing_id = t.id
LEFT JOIN ThingVersion tv2
  ON tv2.thing_id = t.id
  AND tv2.id <> tv1.id
WHERE tv2.id IS NULL

Solution using GROUP BY and COUNT:

SELECT t.* 
FROM Thing t
JOIN ThingVersion tv
  ON tv.thing_id = t.id
GROUP BY t.id
HAVING COUNT(t.id) = 1;

Try them both out for performance.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143