Hello Sheridan Saint-Michel, Let's see.
Advertising
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 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 ----- Original Message ----- From: "Sheridan Saint-Michel" <[EMAIL PROTECTED]> To: "Bas Jobsen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, October 03, 2001 6:34 PM Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > A couple of things here. The problem you are having has nothing to do with > the double left join. The problem is occuring in the left join between > table2 and table3. With the way you have your tables set up > > select table2.id as test,count(table2.no),count(table3.no) from table3 left > join table2 ON table2.id=table3.id group by test; > > will give you the result set > > | test | count(table2.no) | count(table3.no) | > | 1 | 6 | 6 | > > Which is exactly what you are running into with the double join. In order > to fix this you need to rethink the way you have your tables setup. > > Why is id 1 for every field in table2 and table3? The id column should be > used to uniquely identify rows, thereby allowing you to do things like what > you are currently attempting. What is the actual data that you are trying > to represent and why are you doing it in this fashion? Why is table1 even a > part of this select? > > If you were to change table2 and table3 so that id was a unique identifier > like so > > table2 > 1 | 1 > 2 | 1 > > table3 > 1 | 1 > 2 | 1 > 3 | 1 > > You could get the result set you said you wanted > 1 | 2 | 3 > with something like > > select table2.id as test,count(table2.no),count(table3.no) from table3 left > join table2 ON table2.id=table3.id group by table3.no; > > We currently don't have enough to go on, and I think part of the problem is > the design of the tables themselves. Reply with some specifics to the > actual problem... not your proposed solution, and you will probably be more > likely to get help from someone on the list. > > Also, if you do change the id fields in table2 and table3 as I have > suggested you can get your > 1 | 2 | 3 > return set from all three tables using this double join > > select table1.id as test,count(table2.no),count(table3.no) from table3 left > join table2 using(id) left join table1 using(id) group by table3.no; > > Sheridan Saint-Michel > Website Administrator > FoxJet, an ITW Company > www.foxjet.com > > ----- Original Message ----- > From: "Bas Jobsen" <[EMAIL PROTECTED]> > To: "Rick Emery" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Tuesday, October 02, 2001 4:59 PM > Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > > > > Hi Rick, > > > > > please show the REAL output from your query and what it should REALLY > look > > > like > > > > table1: > > id|no > > 1|1 > > 2|1 > > > > table2: > > id|no > > 1|1 > > 1|1 > > > > table3: > > id|no > > 1|1 > > 1|1 > > 1|1 > > > > using: > > SELECT DISTINCT(table1.id) AS test, SUM(table2.no) AS tot, SUM(table3.no) > > FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON > > table2.id=table3.id GROUP BY test ORDER BY tot DESC > > > > gives: > > test|tot|SUM(table3.no) > > 1|6|6 > > 2|0|0 > > > > and i want to have: > > test|tot|SUM(table3.no) > > 1|2|3 > > 2|0|0 > > > > Tnx, > > > > Bas > > > > > > > > > > > > > > ----- Original Message ----- > > From: "Rick Emery" <[EMAIL PROTECTED]> > > To: "'Bas Jobsen'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Tuesday, October 02, 2001 11:11 PM > > Subject: RE: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > please show the REAL output from your query and what it should REALLY > look > > > like > > > > > > -----Original Message----- > > > From: Bas Jobsen [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, October 02, 2001 3:38 PM > > > To: Rick Emery; [EMAIL PROTECTED] > > > Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > > > > SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id > > LEFT > > > > JOIN table3 ON table2.id=table3.id > > > > > > Well, oke now i have: > > > table1: > > > id|no > > > 1|1 > > > 2|1 > > > > > > table2: > > > id|no > > > 1|1 > > > 1||1 > > > > > > table2: > > > id|no > > > 1|1 > > > 1||1 > > > 1||1 > > > > > > The result i want tot have is: > > > id|sum(table2.no)|sum(table3.no)| > > > 1|2|3 > > > 2|0|0 > > > my query: > > > > > > SELECT DISTINT( table1.id) AS test, SUM(table2.no) AS tot, SUM(table3.no > > > FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON > > > table2.id=table3.id GROUP BY test ORDER BY tot DESC > > > > > > which give my something like: > > > id|5|5| > > > 1|2|3 > > > 2|0|0 > > > :( > > > > > > > > > > > > > > > > > > > > > ----- Original Message ----- > > > From: "Rick Emery" <[EMAIL PROTECTED]> > > > To: "'Bas Jobsen'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > > Sent: Tuesday, October 02, 2001 10:19 PM > > > Subject: RE: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > > > > SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id > > LEFT > > > > JOIN table3 ON table2.id=table3.id > > > > > > > > -----Original Message----- > > > > From: Bas Jobsen [mailto:[EMAIL PROTECTED]] > > > > Sent: Tuesday, October 02, 2001 3:13 PM > > > > To: Rick Emery; [EMAIL PROTECTED] > > > > Subject: Re: [PHP-DB] Howto make a double LEFT JOIN > > > > > > > > > > > > > Remove the parentheses > > > > > SELECT table1.id FROM table1 LEFT JOIN table2 LEFT JOIN table3 ON > > > > > table2.id=table3.id ON table1.id=table2.id > > > > > > > > I still got: > > > > MySQL retourneerde: You have an error in your SQL syntax near 'LEFT > JOIN > > > > table3 ON > > > > table2.id=table3.id ON table1.id=table2.id' at line 1 > > > > > > > > -- > > > > 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] > > > > > > > > > -- > > 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]
