Now that was fun and educational. :-) I like the third approach, but theres an added problem to the whole thing. This query is just a part of a bigger search.
The tables, job and color and other fields, which will also be searchable. I am trying to automate the whole process, i mean, the user sees a combo for the fields and a text input, and he can add as many fields as he wants, even duplicated ones, like the two colors problem. I am trying to build a super query that contains all the searches the user wants to do on the tables. Its a stupid job. I understand its an ingrate, and probably stupid, question, but for complex searches like the one i mentioned, is there a line of thought i should consider? I mean, it surely has been done and efficiently, so i am not exactly inventing the wheel here. Its what i said, imagine two/three tables, all joinable by common ids, and allow the user to say "i want to search the name of the job, contains color1 and color2, is from client x and has the perimeter greater than 100". I am teaching php how to do queries all by myself. :-P Thank you all for the great suggestions. Pag On Wed, Dec 17, 2008 at 7:08 PM, Gavin Towey <ga...@swishmark.com> wrote: > Three solutions, the first one is not recommended I just showed it for fun > -- I think the last one is the most efficient: > > mysql> show create table job \G > *************************** 1. row *************************** > Table: job > Create Table: CREATE TABLE `job` ( > `job_id` int(10) unsigned NOT NULL auto_increment, > `name` varchar(12) default NULL, > PRIMARY KEY (`job_id`) > ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 > 1 row in set (0.00 sec) > > mysql> show create table color \G > *************************** 1. row *************************** > Table: color > Create Table: CREATE TABLE `color` ( > `color_id` int(10) unsigned NOT NULL auto_increment, > `color` varchar(32) default NULL, > `job_id` int(10) unsigned default NULL, > PRIMARY KEY (`color_id`) > ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 > 1 row in set (0.02 sec) > > > mysql> select * from job; > +--------+-------+ > | job_id | name | > +--------+-------+ > | 1 | job 1 | > | 2 | job 2 | > | 3 | job3 | > +--------+-------+ > 3 rows in set (0.00 sec) > > mysql> select * from color; > +----------+---------+--------+ > | color_id | color | job_id | > +----------+---------+--------+ > | 1 | yellow | 1 | > | 2 | cyan | 1 | > | 3 | black | 1 | > | 4 | cyan | 2 | > | 5 | magenta | 2 | > | 6 | black | 2 | > | 7 | yellow | 2 | > | 8 | cyan | 3 | > +----------+---------+--------+ > 8 rows in set (0.00 sec) > > Method #1 > > mysql> select job_id, GROUP_CONCAT(color) as colors FROM job j JOIN color c > USING (job_id) GROUP BY job_id HAVING colors LIKE '%cyan%magenta'; > +--------+---------------------------+ > | job_id | colors | > +--------+---------------------------+ > | 2 | black,yellow,cyan,magenta | > +--------+---------------------------+ > 1 row in set (0.01 sec) > > > Method #2 > SELECT j.job_id, c1.color, c2.color FROM job j JOIN color c1 ON > j.job_id=c1.job_id AND c1.color='cyan' JOIN color c2 ON j.job_id=c2.job_id > AND c2.color='magenta'; > +--------+-------+---------+ > | job_id | color | color | > +--------+-------+---------+ > | 2 | cyan | magenta | > +--------+-------+---------+ > 1 row in set (0.00 sec) > > Method #3 > mysql> SELECT job_id, BIT_OR(CASE WHEN c.color='cyan' THEN 1 WHEN > c.color='magenta' THEN 2 END) as colors FROM job j JOIN color c USING > (job_id) GROUP BY job_id HAVING colors=3; > +--------+--------+ > | job_id | colors | > +--------+--------+ > | 2 | 3 | > +--------+--------+ > 1 row in set (0.00 sec) > >
