Table has multiple rows with equal field

I have a table where several rows can have one matching field. For example,

a | 1 | abc
a | 2 | tre
a | 7 | q
b | 4 | g
c | 3 | w
c | 5 | g

How can I create a query that will display field1 which contains two or more field2 specified values. So if I was to, SELECT field1 WHERE field2 = 1 AND field2 = 7 it would output a. Hopefully this isn’t too confusing. Thanks in advance!


Select * from `table` where `field2` IN (1,2)

Is that you meant ?

Can you show me what results you want from the table pattern?

Hmm… I don’t think so. Basically, for input 1 or 1, 2 or 1, 2, 3 I want it to return a. If it was

a | 1 | abc
a | 2 | tre
a | 7 | q
b | 4 | g
c | 1 | w
c | 5 | g

1 should return both a and c while 1, 5 should return only c.

u said 1,5 should return c

so wat abt 5,1 is it same as 1,5 which return c

SELECT field1 
  FROM daTable
 WHERE field2 IN ( 1,7 ) -- 2 values listed
GROUP
    BY field1
HAVING COUNT(*) = 2 -- number of values

Thanks for the response, but I don’t think this is it.
Basically for

a | 1

b | 1
b | 2

c | 1
c | 2
c | 3

I would like 1 to return a, b, c; 1, 2 to return a, b; and 1, 2, 3 to return only c. Hopefully this clears it up.

Actually, with a very slight modification I was able to make this work. Thank you so much!

what was the modification???

Just changed

HAVING COUNT(*) = 2

to

HAVING COUNT(*) >= 2

Basically the same thing but this is what I need. Thank you so much for the help, though!

but >= 2 wouldn’t give the results from post #6?

well, the point of the comments in the query was that you should adjust the count in the HAVING clause to match the number of values you’re looking for

SELECT field1 
  FROM daTable
 WHERE field2 IN ([I] [COLOR="Red"]list of values[/COLOR] [/I]) -- [COLOR="Red"][I]N[/I][/COLOR] values listed
GROUP
    BY field1
HAVING COUNT(*) = [COLOR="red"][I]N[/I][/COLOR] -- number of values

You still do put the number of values, except to fully satisfy my needs it needs to be >= that number rather than =.

yes, i understand

this would be a query to find “at least N” matches

:slight_smile:

So apparently (1,2) should actually produce (b,c) not (a,b)

according to post #12, yes indeedy :slight_smile:

say, did you like my friendly poke earlier on?

for input 1 or 1, 2 or 1, 2, 3 I want it to return a. If it was

a | 1 | abc
a | 2 | tre
a | 7 | q
b | 4 | g
c | 1 | w
c | 5 | g

dear predictionbook, who are you?

do you have a similar question? yours looks different

Off Topic:

Oh, that lol.

I might retort by publicly writing some of my flawed sql querys and announcing I’m a disciple of yours :smiley: