Hello Sheridan Saint-Michel, Well it works fine, tnx!!!
But now i want to understand it too..... bit_count(bit_or(1<<table1.sid)) ?? What does bit mean? I thougt "1<<table1.sid" was something like a backward bit(e)swich stepsize 1?? I creat my sid by: srand((double)microtime()*1000000); $sid = md5(uniqid(rand())); a the sid-field as varchar(32) (i realise now, maybe better use char(32)) (Maybe you will ask why i don't use a auto_increment sid (int(9) or something like that). Cause I don't want the table will be full ever (sid>999.999.999)) Now i thougth bit_count(bit_or(32<<table1.sid)) should do the trick, but it doesn't? Best regards, Bas ----- Original Message ----- From: "Sheridan Saint-Michel" <[EMAIL PROTECTED]> To: "Bas Jobsen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, October 04, 2001 8:02 PM Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > Sheridan Saint-Michel > Website Administrator > FoxJet, an ITW Company > www.foxjet.com > > > ----- 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 > 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 > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
