Page 1 of 1

[SOLVED]Require help on Simple MySQL Select stament

Posted: Tue Aug 30, 2005 12:44 pm
by myleow
i have a relation table with ID and VALUE with the following data are in there

ID VALUES
1 A
1 B
2 B


i am trying to find all ID that have value A & B. I am using mysql and when i tried

Code: Select all

SELECT id FROM relation_table WHERE values = 'A' AND values ='B'
, it returned nothing.

when i do OR instead of AND then ofcourse 2 will come out since 2 has B.

How do i write the Select statement so that when i was the id that contains both A and B?

Thank you in advance.

Regards
Mian

Posted: Tue Aug 30, 2005 12:53 pm
by nielsene

Code: Select all

SELECT id 
  FROM relation_table AS r1 JOIN 
            relation_table AS r2 USING(id)
WHERE  r1.values='A' and r2.values='B';

Posted: Tue Aug 30, 2005 1:04 pm
by myleow
Thanks...

That help on this particular case, but i put down a very simple example because there are more than 2 values A and B. Currently i have 17 unique values and alot of IDs. The method listed above would require 4 JOIN if i have 4 values to match and 10 JOINS if i have 10 values.

Is there a more general query that can do this?

Posted: Tue Aug 30, 2005 1:23 pm
by nielsene
Hmm, I don't beleive there is a good general purpose version of that query, at least not in MySQL. In PostGreSQL, I could probably hack something together using some custom db functions. If you're using PostGreSQL, or Oracle or MS SQL Server, then something along these lines might be possible

Code: Select all

SELECT id, ARRY_LIST(values) as l FROM relation_values GROUP BY id HAVING ARRAY_INTERSECT(l,$list)=l
where both ARRAY_LIST and ARRAY_INTERSECT are user defined extensions to the DBMS.... (not a trivial thing)

Or this might work in MySQL (and others) ....possibly:

Code: Select all

SELECT id, count(DISTINCTvalues) AS c FROM relation_tables WHERE vales IN ($valueList) GROUP BY id HAVING c=$numItemsInValueList;

Posted: Tue Aug 30, 2005 1:56 pm
by myleow
Thanks... It works. I used to use PostGreSQL but this job requires MySQL but my SQL skill is not where as leet as yours!