CODEWITHSUNDEEP
sqloracle-database
Tom
Tom

Reputation: 45

Aggregate function in where clause

I have two tables: TITLE and BACKGROUND

BACKGROUND has a foreign key into TITLE so I'm trying to write a query that will return any TITLE rows that have more than one matching BACKGROUND.

SELECT T.ID
  FROM TITLE T, BACKGROUND B
 WHERE T.ID = B.TITLE_ID
   AND /* there are multiple matching background rows */

Upvotes: 2

Views: 6476

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

Have you tried.....

select T.ID from (
    SELECT T.ID,B.TITLE_ID, count(*)
      FROM TITLE T, BACKGROUND B
      WHERE T.ID = B.TITLE_ID
      group by T.ID,B.TITLE_ID
      having count(*) > 1
)

Upvotes: 1

mevdiven
mevdiven

Reputation: 1902

select t.id
  from title t
 where exists (select * 
                 from background b
                where b.title_id = t.id
                having count(*) > 1 )

Upvotes: 3

Related Questions