[SOLVED]Require help on Simple MySQL Select stament

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
myleow
Forum Contributor
Posts: 194
Joined: Mon Jun 21, 2004 7:05 pm
Location: California

[SOLVED]Require help on Simple MySQL Select stament

Post 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
Last edited by myleow on Tue Aug 30, 2005 2:10 pm, edited 1 time in total.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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';
myleow
Forum Contributor
Posts: 194
Joined: Mon Jun 21, 2004 7:05 pm
Location: California

Post 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?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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;
myleow
Forum Contributor
Posts: 194
Joined: Mon Jun 21, 2004 7:05 pm
Location: California

Post 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!
Post Reply