CODEWITHSUNDEEP
mysqlsql
aslingga
aslingga

Reputation: 1409

How do I use IN operator but with AND condition

I have two tables Person and Interest

Person table structure

Id | Name

Interest table structure

Id | Person Id | Interest Category

My question is how do I get person who have a liking for the category 'music' AND 'movie' AND 'sport'. The category will be dynamically.

So it will like using IN operator but by using AND condition not OR condition in the IN operator.

SELECT `Person Id` FROM Interest
WHERE `Interest Category` IN ('music', 'movie', 'sport');

Upvotes: 1

Views: 311

Answers (3)

Rohan
Rohan

Reputation: 2030

try this


SELECT DISTINCT DECODE(A.RES, B.RES, I.INTEREST_CATEGORY, 'FALSE') AS FINAL_RES
  FROM INTEREST I,
       TABLE(SYS.DBMS_DEBUG_VC2COLl('music', 'movie', 'sport')),
       (SELECT LENGTH(WM_CONCAT(COLUMN_VALUE)) AS RES
          FROM TABLE(SYS.DBMS_DEBUG_VC2COLl('music', 'movie', 'sport'))) A,



   (SELECT LENGTH(WM_CONCAT(DISTINCT I.INTEREST_CATEGORY)) AS RES
      FROM TABLE(SYS.DBMS_DEBUG_VC2COLl('music', 'movie', 'sport')),
           INTEREST I
     WHERE I.INTEREST_CATEGORY = COLUMN_VALUE) B




WHERE I.INTEREST_CATEGORY = COLUMN_VALUE

This query has 2 parts 


(SELECT LENGTH(WM_CONCAT(COLUMN_VALUE)) AS RES
          FROM TABLE(SYS.DBMS_DEBUG_VC2COLl('music', 'movie', 'sport')))
This calculates the length of the values provided as input

The 2nd query 


(SELECT LENGTH(WM_CONCAT(DISTINCT I.INTERESET_CATEGORY)) AS RES
          FROM TABLE(SYS.DBMS_DEBUG_VC2COLl('music', 'movie', 'sport')),
               INTEREST I
         WHERE I.INTEREST_CATEGORY = COLUMN_VALUE)
calculates the length of the categories matched with the database entries.

Then these 2 lengths are finally compared and the IDs are returned.

This is a real long way to get this done, but in case you do not want to use group by(which is far smaller coding), this may be a method for your query.

Hope it helps

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55624

You could use HAVING COUNT. The number would have to be dynamically too.

SELECT person_id
FROM interest
WHERE interest_category IN ( 'music', 'movie', 'sport');
GROUP BY person_id
HAVING COUNT(*) = 3;

Upvotes: 3

xdazz
xdazz

Reputation: 160973

SELECT DISTINCT p.id
FROM Person p
JOIN Interest i1 ON p.id = i1.person_id AND i1.interest_category = 'music'
JOIN Interest i2 ON p.id = i2.person_id AND i2.interest_category = 'movie'
JOIN Interest i3 ON p.id = i3.person_id AND i3.interest_category = 'sport'

Upvotes: 2

Related Questions