Re: Howto make a double LEFT JOIN

Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com ----- Original Message ----- From: "Bas Jobsen" <bas@startpunt.cc> To: "Sheridan Saint-Michel" <webmaster@foxjet.com>; <php-db@lists.php.net> Sent: Wednesday, October 03, 2001 2:52 PM Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > Hello Sheridan Saint-Michel, > > Let's see. > > Table2 contains data with a unique identifier (id) > b.e > id|url > > Table1 and Table3 contain the same identifier (id) but it isn't unique now. > Table1 and Table3 contain couting of action. Cause i want to count only the > actions of the last X hours, each count is on a row apart. Each row in this > table contains a timestamp, so i could do something like "DELETE FROM Table1 > WHERE time<Y" (Oke, so Table1 (Table3 ) must have an other unique identifier > (sid)). > > Table1 and Table3: > sid|id|count|time > > count is always 1 You probably don't need count, as each entry implies one event, unless you are using it for something else I don't know about... otherwise this looks good. > Oke, what do i want? > > First i want to know which id's are in Table1 and how many times (know not > all the id's from Table2 have to be in Table1(or in Table3, and not all the > id's from Table1 have to be in Table3)) > > something like: "SELECT DISTINCT id, SUM(count) FROM table1 GROUP BY id" > > For each id found in Table1 i want to know two things: > > 1) the url from Table2 > 2) is there any row in Table3 where table1.id=table3.id and if so, how many > are there? > > This is it. > > thanks!!!, > > Bas Okay, now that you have added a unique Primary Key to each table this becomes a doable task. The query to do everything you list here should be: select table1.id,bit_count(bit_or(1<<table1.sid)) as count1,bit_count(bit_or(1<<table3.sid)) as count2,table2.url as url from table1 left join table3 using(id) left join table2 using(id) group by table1.id; As you can see, count is never used... which is why I said you probably don't need it. Let me know if this helps Sheridan Saint-Michel Website Administrator FoxJet, an ITW Company www.foxjet.com