Re: MySQL query/PHP logic?

Don't know whether it's just because it's late at night here in the UK or whether I'm being a bit dim, but wouldn't it be much, much easier to store all the data in your non-main tables in a single table and distinguish the type of data (i.e. fof, pub, gov, etc....) by a field. This would simplify your query and no doubt improve performance. On Tuesday 29 Jul 2003 10:13 pm, Petre Agenbag wrote: > Hi List > OK, I've posted this on the MySQL list as well, but it seems a bit quiet > there, and arguably it could definately be a PHP rather than mysql question > depending on whether it can be done with one query (then it was/is a mysql > query), or whether it should be done with some structures and multiple > queries ( then it's arguably PHP). > > So, here goes. > > > I'm trying to return from multiple tables, the records that have field > "information_sent" between two dates. > The tables are all related by means of the id of the entry in the main > table, ie.. > > main > id entity_name ... > > fof > id_fof id information_sent ... > > pub > id_pub id information_sent ... > > etc. > > So, I tried the following join > > select * from main > left join fof on main.id = fof.id > left join pub on main.id = pub.id > left join gov on main.id = gov.id > left join med on main.id = med.id > left join ngo on main.id = ngo.id > left join own on main.id = own.id > left join sup on main.id = sup.id > left join tra on main.id = tra.id > where ( > (fof.information_sent > '$date1' and fof.information_sent < > '$date2') > OR > (pub.information_sent > '$date1' and pub.information_sent < > '$date2') > OR > (gov.information_sent > '$date1' and gov.information_sent < > '$date2') > OR > (med.information_sent > '$date1' and med.information_sent < > '$date2') > OR > (ngo.information_sent > '$date1' and ngo.information_sent < > '$date2') > OR > (own.information_sent > '$date1' and own.information_sent < > '$date2') > OR > (sup.information_sent > '$date1' and sup.information_sent < > '$date2') > OR > (tra.information_sent > '$date1' and tra.information_sent < > '$date2') > ) > order by entity_name > > > BUT, although it seems to be "joining" the tables correctly AND only > returning the ones with the correct date criteria, it does NOT return > the "id" or the "information_sent" fields correctly ( due to duplication > in the result ) > > Can this be done in one query without sub-selects, or should it be broken > up (in which case I would still need help with the logic and to minimize > the amount of queries inside loops) > > > Thanks