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!

Chris Tine
  • 3
  • 2
  • I'm finding it hard to follow your question, could you elaborate with a mocked up example? – Jonathan Fite Jul 11 '22 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 Jul 11 '22 at 16:18
  • 1
    Please [edit] your question to include the information in your comment, and use the markup for pieces of code. – Mark Stewart Jul 11 '22 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 Jul 11 '22 at 17:12
  • Read and clicked the check mark next to the answer. – Chris Tine Jul 11 '22 at 17:14

1 Answers1

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.

Andrew Leach
  • 126
  • 2
  • 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 Jul 11 '22 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. – Andrew Leach Jul 11 '22 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 Jul 11 '22 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 Jul 11 '22 at 17:15