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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
