Petre,
What I think you want is,
For each main, with at least one fof,pub... in the date range,
a row with the id and for fof,pub a value within range or null
What you are probably getting is,
For each main, with at least one fof,pub... in the date range,
several rows, where fof,pub,... are null of have some value,
not necessarily in range, but at least one fof,pub,... is
in range.
I assume that's what you mean by "duplication in the result"
Consider what left join means:
select T1 left join T2 on condition
returns the following rows:
(1) select T1, T2 where condition
(2) a row for each T1 which was not matched in (1), with nulls for T2
Any where clauses are applied after the inner join, and serve to
further restrict the rows returned.
What you probably want is:
select * from main
left join fof on main.id = fof.id
and (fof.information_sent > '$date1' and fof.information_sent <
'$date2')
left join pub on main.id = pub.id
and (pub.information_sent > '$date1' and pub.information_sent <
'$date2')
...
where fof.id is not null
OR pub.id is not null
...
The resulting rows will have only matching dates (or null), which
I suspect is what you want.
Notes:
1. If there are 2 matching dates for fof and 2 matching dates for pub,
you will still get all 4 combinations.
2. The where clause gets rid of the main.id's where none of the other
tables has a matching date; you may or may not want this.
HTH,
Bill
> Subject: Left Join multiple tables
> From: Petre Agenbag <[EMAIL PROTECTED]>
> Date: 29 Jul 2003 16:17:05 +0200
>
> Hi List
> Me again.
>
> 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 )
>
> What am I doing wrong?
>
> Thanks
>
>
> mysql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
