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
r937
March 22, 2010, 1:24pm
5
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!
r937
March 23, 2010, 2:06am
8
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 ?
r937
March 23, 2010, 9:08am
11
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
r937:
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 =.
r937
March 24, 2010, 2:12am
13
yes, i understand
this would be a query to find “at least N” matches
dimkasmir:
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.
So apparently (1,2) should actually produce (b,c) not (a,b)
r937
March 24, 2010, 2:53am
15
according to post #12 , yes indeedy
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
r937
March 24, 2010, 3:09am
17
dear predictionbook, who are you?
do you have a similar question? yours looks different