Select rows Having Count in linked table

Question

I have what I suspect is an easy problem, but my sql skills just aren't there, and Google is not helping me (yet).

I have the following tables (stripped down for ease of reading):

CREATE TABLE IF NOT EXISTS `PROPERTIES` (
    `ID` int(11) NOT NULL auto_increment,
    PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111 ;

CREATE TABLE IF NOT EXISTS `PROPERTY_PHOTOS` (
  `ID` int(11) NOT NULL auto_increment,
  `PROPERTY_ID` int(11) NOT NULL,
  `PHOTO` varchar(128) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=911 ;

Which link on PROPERTIES.ID = PROPERTY_PHOTOS.PROPERTY_ID

What I would like to do is something like:

SELECT P.ID FROM PROPERTIES P WHERE COUNT(SELECT PP.ID FROM PROPERTY_PHOTOS PP WHERE PP.PROPERTY_ID = P.ID) > 0

or

SELECT P.ID FROM PROPERTIES P HAVING COUNT(SELECT PP.ID FROM PROPERTY_PHOTOS PP WHERE PP.PROPERTY_ID = P.ID) > 0

However, both queries are syntactically incorrect.

Can anyone advise on how I would select only records from PROPERTIES that have corresponding records in the PROPERTY_PHOTOS table?

score 2 · Accepted Answer · answered Jul 14 '11 at 18:51

2

    SELECT P.ID,COUNT(*)
      FROM PROPERTIES P
         , PROPERTY_PHOTOS PP
     WHERE PP.PROPERTY_ID = P.ID 
   GROUP BY P.ID
    HAVING COUNT(*) > 0

Or actually, a regular join should work if you don't care about the count

SELECT DISTINCT P.ID
  FROM PROPERTIES P
     , PROPERTY_PHOTOS PP
 WHERE PP.PROPERTY_ID = P.ID

answered Jul 14 '11 at 18:51

dcp

54,410
22
144
164

beat me to it and your answer is better – Jim Jeffries Jul 14 '11 at 18:56
@jamesj - Thanks for the compliment :) – dcp Jul 14 '11 at 18:57
Oh man, I REALLY over-thought this one. Thank you for slapping some sense into me. – DaveL Jul 14 '11 at 19:18

score 1 · Answer 2 · answered Jul 14 '11 at 18:54

1

This should do it

 SELECT PROPERTIES.ID, COUNT(*) FROM PROPERTIES, PROPERTY_PHOTOS WHERE PROPERTY_PHOTOS.PROPERTY_ID = PROPERTIES.ID GROUP BY PROPERTIES.ID HAVING COUNT(*) > 0

answered Jul 14 '11 at 18:54

Jim Jeffries

9,841
15
62
103

Select rows Having Count in linked table

2 Answers2

Linked