RE: [PEAR] DB_DataObject, complicated "Select"

Hi ! I've advanced something, but I still have some doubts. First, I've re build the problem: I have a table, with 2 fields: Rel_Countries ----------------------- id_cover id_country ----------------------- 1 10 1 14 1 12 2 7 2 10 2 11 3 8 3 10 3 9 3 7 3 11 --------------------- And I want to retrive id_cover records that: id_country is equal to 10, 7 and 11. The query must answer: id_cover 2, and id_cover 3 --------- id_cover --------- 2 3 --------- I've found a solution, but is only SQL based and a bit ugly: --------------------------------------------- $covers=new DataObjects_Rel_countries(); $covers->query("SELECT p1.id_cover FROM rel_countries AS p1 " ."INNER JOIN rel_countries p2 USING(id_cover) " ."INNER JOIN rel_countries p3 USING(id_cover) " ."WHERE p1.id_country=10 " ."AND p2.id_country=7 " ."AND p3.id_country=11 " ."AND p2.id_country > p1.id_country " ."AND p3.id_country > p1.id_country"); while($covers->fetch()) { echo("cover: ".$covers->id_cover."<br>\n"); }; } --------------------------------------------- In this example, I have retrived records covers that haves 3 distincts id_countries. But If I want to retrive records covers that haves 100 countries, I would need to make 100 JOINS. there is a easier way to do it ? Thank you! mac -- ...ThE PhP_KiD. -> -----Mensaje original----- -> De: ThE PhP_KiD [mailto:gregoriandres@yahoo.com.ar] -> Enviado el: Lunes, 02 de Junio de 2003 03:11 p.m. -> Para: PEAR general list -> Asunto: [PEAR] DB_DataObject, complicated "Select" -> -> -> Hi, DB_DataObjects Users...! -> -> I've a two tables related, called -> "covers", and "rel_covers_countries" -> -> Covers -> ----------------------- -> id cover -> ----------------------- -> 1 napoleon, 1870 -> 2 san martin, 1820 -> 3 garibaldi, 1420 -> ......... -> ----------------------- -> -> -> And... -> -> rel_cover_countries -> --------------------- -> id_cover id_country -> --------------------- -> 1 10 -> 1 14 -> 1 12 -> 2 10 -> 2 11 -> 3 8 -> 3 10 -> 3 9 -> 3 11 -> --------------------- -> -> -> In last table you have all countries which -> covers are related. -> -> -> I need to select covers, with next criterion: -> -> (example:) -> -> If I want to get covers which haves -> id_country: 10 and 11, then database must -> returns me the id_cover 2, and 3. -> -> How can reach this result with DataObjects ? -> (or with SQL). -> -> -> Help me, please !!! -> -> mac -> -- -> ...ThE PhP_KiD. -> -> -- -> PEAR General Mailing List (http://pear.php.net/) -> To unsubscribe, visit: http://www.php.net/unsub.php