0

There are two tables C and B.

They're connected by C.id=B.cid.

I need to find all the C.id es where:

  • None of the rows corresponding to cid has is_true='Y'

I've tried various queries but can't wrap my head around any of them. This is one of my ideas.

select * from 
C inner join B 
on C.id=B.cid 
where
-- loop all rows of B using B.cid 
-- output if the row has is_true!='Y'

enter image description here

6
  • The problem is I've to obfuscate the data else I'm gonna get fired. obfuscating is insanely time consuming. I'm working on it.
    – barnyard9
    Oct 31 at 10:37
  • Why not encrypt it Oct 31 at 11:07
  • 1
    "obfuscating is insanely time consuming." - You've already provided an example query for 2 imaginary tables. It shouldn't take anyone more than 15 minutes to provide a couple of made up rows with different example data that would fit a couple of different scenarios in your case. Otherwise, as you can see, it's hard for someone to give you a meaningful answer.
    – J.D.
    Oct 31 at 12:07
  • Ok data is ready. I'm looking how to create a table.
    – barnyard9
    Oct 31 at 12:11
  • 1
    Put the data above. Please check. @J.D.
    – barnyard9
    Oct 31 at 12:15

2 Answers 2

0
SELECT c.id
FROM c
WHERE NOT EXISTS (
    SELECT NULL
    FROM b
    WHERE b.cid = c.id
      AND is_true='Y'
    )
id
2
5

fiddle

3
0

Since all you're looking for is the key field, C.id aka B.cid, then you can accomplish your goal simply with a GROUP BY clause like so:

SELECT cid
FROM B
GROUP BY cid
HAVING MAX(is_true) = 'N'

Simplified example dbfiddle to demonstrate.

2
  • Maybe MAX(is_true) = 'N' is enough?
    – Akina
    Oct 31 at 12:38
  • @Akina Yes, you're right. Thanks!
    – J.D.
    Oct 31 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.