* ds
> > Hi, please help me with this problem...
> >
> > Suppose i have a table whith ids and two more tables with logs like
> > this:
> >
> > table0: id1,id2
> > table1: id1,id2,date,ip
> > table2: id1,id2,date,ip
> >
> > Table0 is where i have all existent pairs (id1,id2).
> > Table1 logs all events of type 1.
> > table2 logs all events of type 2.
> >
> > My problem is that i need to left join table0 to table1 and table2.
> > I'm doing something like:
> >
> > SELECT table0.id1,table0.id2,count(table1.ip),count(table2.ip)
> > FROM table0
> > LEFT JOIN table1 USING (id1,id2)
> > LEFT JOIN table2 USING (id1,id2,date)
> > ..
> >
> > but this forgets rows i have in table2 that are not in table1.
> > What i want is something like:
> >
> > ___________________ table1
> > / left join
> > table0
> > \___________________ table2
> > left join
> >
> > Is it possible ?
Yes. Use ON instead of USING:
SELECT table0.id1,table0.id2,count(table1.ip),count(table2.ip)
FROM table0
LEFT JOIN table1 USING (id1,id2)
LEFT JOIN table2 ON
table2.id1 = table0.id1 and
table2.id2 = table0.id2
WHERE ...
(If you include a test on the date field you will loose all rows from table2
which are not in table1, as I understand you that is not what you want.)
--
Roger
query
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
