CODEWITHSUNDEEP
mysql
Jesse Clark
Jesse Clark

Reputation: 133

IN Clause selects all variables

When I run this procedure:

SELECT * FROM photo 
LEFT JOIN photo_selectedTags
ON photo.COUNTER = photo_selectedTags.PHOTO_COUNTER
WHERE photo_selectedTags.TAGS_COUNTER IN (6,192)

It retrieves rows where TAGS_COUNTER = 6 OR 192

How do I retrieve the rows from 'photo' where TAGS_COUNTER = 6 AND 192?

Corrected: the IN for ALL

Upvotes: 0

Views: 107

Answers (3)

dnsmkl
dnsmkl

Reputation: 792

I would propose to use 2 joins

SELECT *
FROM photo

JOIN photo_selectedTags as photo_selectedTags6 -- this join restricts to 'photo.COUNTER' whic have TAGS_COUNTER = 6
ON photo.COUNTER = photo_selectedTags6.PHOTO_COUNTER
AND photo_selectedTags6.TAGS_COUNTER = 6

JOIN photo_selectedTags as photo_selectedTags192 -- this join restricts to 'photo.COUNTER' whic have TAGS_COUNTER = 192
ON photo.COUNTER = photo_selectedTags192.PHOTO_COUNTER
AND photo_selectedTags192.TAGS_COUNTER = 192



Also would be possible to achive it with analytical functions (if supported by your DB)

-- This one works on teradata. Something similar should work on oracle. Don't know about others
SELECT *
FROM photo 
LEFT JOIN photo_selectedTags
ON photo.COUNTER = photo_selectedTags.PHOTO_COUNTER
QUALIFY max(case when photo_selectedTags.TAGS_COUNTER = 6 then 1 end) over (partition by photo.COUNTER) = 1
AND max(case when photo_selectedTags.TAGS_COUNTER = 192 then 1 end) over (partition by photo.COUNTER) = 1

If you have many values in the list (in addition to 192,6), then this might be possible solution

SELECT *
FROM photo
JOIN
(
    SELECT PHOTO_COUNTER, count(distinct TAGS_COUNTER) cnt
    FROM photo_selectedTags
    WHERE TAGS_COUNTER in (192,6)
    HAVING cnt = 2 -- adjust this according to the number of different values
) as pht
ON photo.COUNTER = pht.PHOTO_COUNTER

In subquery only PHOTO_COUNTERs are left which have both (192 and 6), then this is joined

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171569

The basic idea is this:

SELECT PHOTO_COUNTER
FROM photo_selectedTags 
WHERE TAGS_COUNTER in (6, 192)
group by PHOTO_COUNTER
having count(distinct TAGS_COUNTER) = 2 --2 matches # of items in IN clause

You can then do this to get the rest of the columns:

SELECT *
from PHOTO_COUNTER 
LEFT JOIN photo_selectedTags
ON photo.COUNTER = photo_selectedTags.PHOTO_COUNTER
where photo.COUNTER in (
    SELECT PHOTO_COUNTER
    FROM photo_selectedTags 
    WHERE TAGS_COUNTER in (6,192)
    group by PHOTO_COUNTER
    having count(distinct TAGS_COUNTER) = 2 --2 matches # of items in IN clause
) a

Upvotes: 1

DonCallisto
DonCallisto

Reputation: 29932

Edit

Now that i understand what you want and DB structure, try with this:

SELECT * FROM photo 
LEFT JOIN photo_selectedTags
ON photo.COUNTER = photo_selectedTags.PHOTO_COUNTER
WHERE photo_selectedTags.TAGS_COUNTER = 6 AND photo_id IN
(SELECT photo_id FROM photoSELECT * FROM photo 
LEFT JOIN photo_selectedTags
ON photo.COUNTER = photo_selectedTags.PHOTO_COUNTER
WHERE photo_selectedTags.TAGS_COUNTER = 192)

I don't know if photo_id is an actual field of your table, but try to adapt it to your structure

Obviously in the first SELECT don't insert PHOTO_COUNTER because i'll be always the same value and it haven't much sense.

Upvotes: 0

Related Questions