IN clause with - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

IN clause with

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-16 : 08:01:25

Ricardo writes "I'm sorry about the subject above but I really don't how to explain my question without providing an example.

I have to tables, for example, photos and people on the photos:

Table 1 contains, for example:
Cod Photo
-----------------
1 Photo 1
2 Photo 2
3 Photo 3

Table 2 contains, for example:
Cod Cod_Photo Person
----------------------------
1 1 John
2 1 Smith
3 2 Mary
4 2 John
5 2 Smith
6 3 Richard
7 3 Smith

My question is: How can I, for example, show all the photos containing John AND Smith on it?

I know if I use something like: SELECT FROM PHOTOS, PEOPLE WHERE PHOTOS.COD_PHOTO = PEOPLE.COD_PHOTO AND PEOPLE.COD IN (1, 2) the result will give me records where either one or the other or both appears and I want only the photos that both appears together.

I hope you can understand and help me.

Thank you,
Ricardo"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-12-16 : 08:06:29

select t1.photo
from table1 t1 inner join table2 t2
on t1.cod = t2.cod_photo
where t2.person in ('john','smith')

oops, misread the question ... nr is correct

Jay White
{0}

Edited by - Page47 on 12/16/2002 08:11:38

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-16 : 08:09:00

SELECT * FROM PHOTOS
where exists
(select PEOPLE.COD from PEOPLE
where PEOPLE.COD_PHOTO = PHOTOS.COD_PHOTO
and PEOPLE.COD IN (1, 2)
group by PEOPLE.COD
having count(*) = 2
)

oops
SELECT * FROM PHOTOS
where 2 =
(select count(*) from PEOPLE
where PEOPLE.COD_PHOTO = PHOTOS.COD_PHOTO
and PEOPLE.COD IN (1, 2)
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 12/16/2002 11:04:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources