0

Hoping for syntax to select for result that has both of 2 possible column values.

Right now I'm doing select distinct f1,f2,f3 from table where f3 in ('val1','val2')

and outputting the text file and then looking for the cases where both val1 and val2 are in the file for the same f1,f2. f3 can be val1, val2, val?... with one line in the table for each of the values. thanks!

5
  • I'm finding it hard to follow your question, could you elaborate with a mocked up example? Commented Jul 11, 2022 at 15:54
  • select distinct program,observation,slit from nirspec_exposure_specification where slit in ('S200A1','S200A2') want where slit = 'S200A1' and where slit = 'S200A2' for same program,observation
    – Chris Tine
    Commented Jul 11, 2022 at 16:18
  • 1
    Please edit your question to include the information in your comment, and use the markup for pieces of code. Commented Jul 11, 2022 at 16:54
  • My apologies for not including a mocked up output of the data in the table. I'll try and do better if I ask another question. I'm having trouble figuring out how to accept Andrew's answer. I don't see an 'accept' button or similar anywhere.
    – Chris Tine
    Commented Jul 11, 2022 at 17:12
  • Read and clicked the check mark next to the answer.
    – Chris Tine
    Commented Jul 11, 2022 at 17:14

1 Answer 1

1

I've mocked up this table...

r f1 f2 f3
1 A B val1
2 A B val2
3 A B val1
4 A E val1
5 A E val2
6 A G val1
7 A G val1

...and I assume you want to find all combinations of (f1,f2) which have 'val1' or 'val2' in f3.

Thus, from that table, you would expect to get the combinations (A,B) and (A,E) but not (A,G) because the last combination doesn't feature 'val2'.

First, get unique combinations having either 'val1' or 'val2' in f3.

SELECT DISTINCT f1,f2,f3 FROM table1 WHERE f3 IN ('val1','val2')

Once you have that, then group the rows on the first two columns and count how many of each combination you have. You want combinations where there are two (which must be both 'val1' and 'val2'):

SELECT f1,f2 FROM
   (
   SELECT DISTINCT f1,f2,f3 FROM table1 WHERE f3 IN ('val1','val2')
   ) q
GROUP BY f1,f2
HAVING COUNT(f3)=2

This will get you

f1 f2
A B
A E

With nested queries this isn't going to be very efficient. Optimising depends at least to some extent on the amount of data, its variability and which database you're using.

4
  • Thank you thank you thank you!!! That worked perfectly. Got the same output as I did looking through my output file manually. I really appreciate it.
    – Chris Tine
    Commented Jul 11, 2022 at 16:59
  • @ChrisTine Great! Do note the comments on the question regarding what information is needed: you know what you're asking and what data you have, but no-one else does. I made a lucky guess. Don't forget you can accept an answer. And don't forget that someone might come up with a better answer too. Commented Jul 11, 2022 at 17:03
  • I'm sorry... Next time I'll add data output from the table as you simulated. I'll try to figure out how to accept your answer.
    – Chris Tine
    Commented Jul 11, 2022 at 17:07
  • Read and clicked the check mark next to the answer to accept it. Thank you again especially for the extra effort you put in to compensate for my lack of information.
    – Chris Tine
    Commented Jul 11, 2022 at 17:15

Your Answer

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

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