CODEWITHSUNDEEP
mysqlsql
Ceeee
Ceeee

Reputation: 1442

Do not retrieve if all or one of the supplied category is existing

I have below database and my problem is that I want to query all list_id in my_list table where its corresponding list_risk_code is '600' and its info_risk_code are all '400'

table: my_info_list
info_id        list_id
1              1
2              1
3              1
4              2
5              2
6              3
7              3

table: my_info 
info_id        info_risk_code
1              '400'
2              '600'
3              '400'
4              '600'
5              '600'
6              '400'
7              '400'


table: my_list
list_id        list_risk_code
1              '600'
2              '600'
3              '600'

My desired output below, because, list_id 1 has 3 info_risk_code(400,600 and 400) but one of them is 600 so it wont be included. the list_id 2 has 2 info_risk_code(600,600) but both of them are 600 so it is disregarded also. only list_id 3 is retrieve because it has 2 info_risk_code which are both 400: 

my_list.list_id        info_risk_code     list_risk_code
3                      '600'              '400'

right now my code is below which gets 1 and 3 for they both contain non-600 info_risk_code. which is incorrect :

SELECT DISTINCT
    ml.list_id
   ,info_risk_code as c_rr
   ,list_risk_code as a_rr
FROM 
    my_list AS ml
    INNER JOIN my_info_list AS mil ON mil.list_id = ml.list_id
    INNER JOIN my_info AS mi ON mil.info_id = mi.info_id
WHERE 
    (
            (info_risk_code = '600' OR info_risk_code = '360') 
        AND (NOT list_risk_code = '600' AND NOT list_risk_code = '360')
    )
    OR 
    (
            (NOT info_risk_code = '600' AND NOT info_risk_code = '360') 
        AND (list_risk_code = '600' OR list_risk_code = '360')
    )

for your trial/reference you can use this:

CREATE TABLE my_info_list(
    info_id INT,
    list_id INT
);

CREATE TABLE my_info(
    info_id INT,
    info_risk_code varchar(5)
);

CREATE TABLE my_list(
    list_id INT,
    list_risk_code varchar(5)
);


INSERT INTO my_info_list VALUES (1,1);
INSERT INTO my_info_list VALUES (2,1);
INSERT INTO my_info_list VALUES (3,1);
INSERT INTO my_info_list VALUES (4,2);
INSERT INTO my_info_list VALUES (5,2);
INSERT INTO my_info_list VALUES (6,3);
INSERT INTO my_info_list VALUES (7,3);

INSERT INTO my_info VALUES (1,'400');
INSERT INTO my_info VALUES (2,'600');
INSERT INTO my_info VALUES (3,'400');
INSERT INTO my_info VALUES (4,'600');
INSERT INTO my_info VALUES (5,'600');
INSERT INTO my_info VALUES (6,'400');
INSERT INTO my_info VALUES (7,'400');

INSERT INTO my_list VALUES (1,'600');
INSERT INTO my_list VALUES (2,'600');
INSERT INTO my_list VALUES (3,'600');

Coding for almost an hour now with this query, need some idea. Thank you

Upvotes: 2

Views: 62

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

You can use a group by query to only select list_id values where all info_risk_codes are 400 and the associated list_risk_code is 600

select ml.list_id 
from my_list ml 
join my_info_list mil on ml.list_id = mil.list_id
join my_info mi on mil.info_id = mi.info_id
where ml.list_risk_code = '600'
group by ml.list_id
having sum(info_risk_code <> '400') = 0

http://sqlfiddle.com/#!9/4b9e9/1

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use NOT EXISTS:

SELECT DISTINCT ml.list_id, info_risk_code as c_rr, list_risk_code as a_rr
FROM my_list AS ml
INNER JOIN my_info_list AS mil
  ON mil.list_id = ml.list_id
INNER JOIN my_info AS mi
  ON mil.info_id = mi.info_id
WHERE mi.info_risk_code = '400' AND 
      NOT EXISTS (SELECT 1
                  FROM my_info_list AS mil2
                  INNER JOIN my_info AS mi2 ON mil2.info_id = mi2.info_id
                  WHERE mil2.list_id = ml.list_id AND mi2.info_risk_code <> '400')

Demo here

Upvotes: 1

Related Questions