选择链接表中有计数的行 [英] Select rows Having Count in linked table

问题描述

我怀疑是一个简单的问题，但我的 sql 技能不存在，而且 Google 没有帮助我(目前).

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 ;

PROPERTIES.ID = PROPERTY_PHOTOS.PROPERTY_ID 上的链接

我想做的是:

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

或

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.

谁能建议我如何只从 PROPERTIES 中选择在 PROPERTY_PHOTOS 表中有相应记录的记录?

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

推荐答案

    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 

这篇关于选择链接表中有计数的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！