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!