Sorry i replied from a different address. Heres the reply with some editing:
Got it to work perfectly, thanks, although it takes some time (CILS has >150.000 records). There was no need for unique since all the results are already surprisingly unique. Thank you, it was a different way of doing things. Well, different to me, at least. :-) In the meantime, i found an alternative way of doing things, although honestly i think its a bit "dirty" (prod_enc is JOB, prod_enc_cil is CILS): select t1.num_of from (select pe.num_of,GROUP_CONCAT(cast( pec.id_cor as char) ORDER BY pec.id_cor DESC SEPARATOR '|') cores from prod_enc pe join(prod_enc_cil pec) on(pe.num_of=pec.num_of) group by pe.num_of order by pe.num_of desc) t1 where cores like '%cyan%magenta%' I cant get it to work if i only keep the inner select: select pe.num_of,GROUP_CONCAT(cast(pec.id_cor as char) ORDER BY pec.id_cor DESC SEPARATOR '|') cores from prod_enc pe join(prod_enc_cil pec) on(pe.num_of=pec.num_of) group by pe.num_of order by pe.num_of desc where cores like '%cyan%magenta%' Like this it complains about not having a column called "cores", which i find very weird. Any insight on that? And yet another way, although it results in a different record count because it catches records with 2xcyan, 2xmagentas, and cyans and magentas >2. select num_of, count(num_of) c from prod_enc_cil where id_cor='cyan' or id_cor='magenta' group by num_of having c>1 order by num_of desc What this does is list all CILS that have cyan or magenta, and shows the num_of that have more hits then 1 (thus have both colors or several hits of either colors). Pag PS - sorry, ceo, for making you receive so many copies. :-P On Wed, Dec 17, 2008 at 3:15 PM, <c...@l-i-e.com> wrote: > > Hopefully your CILS table is not too many rows... > > select * from JOB, CILS as cyan, CILS as magenta > where cyan.num_of = JOB.num_of > and magenta.num_of = cyan.num_of > and cyan.color = 'cyan' > and magenta.color = 'magenta' > > or something not unlike that... > > You may want UNIQUE JOB.id_enc or somesuch, because this will get TWO JOBs > each, since one is cyan and one is magenta. > > If any of these tables are "large" this could be an enormous number of temp > records. > > Do an "explain" on the query to see just what sort of pain you are looking > at... > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=manifest...@gmail.com > >
