Sheridan Saint-Michel
Website Administrator
FoxJet, an ITW Company

----- Original Message -----
From: "Bas Jobsen" <[EMAIL PROTECTED]>
To: "Sheridan Saint-Michel" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
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
> Table1 and Table3 contain couting of action. Cause i want to count only
> actions of the last X hours, each count is on a row apart. Each row in
> table contains a timestamp, so i could do something like "DELETE FROM
> WHERE time<Y" (Oke, so Table1 (Table3 ) must have an other unique
> (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
> all the id's from Table2 have to be in Table1(or in Table3, and not all
> 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 and if so, how
> 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,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;

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

PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to