Anne, Actually, you can group by a foreign table. When you say "it breaks", what exactly do you mean? What results do you get? What results did you expect? What is your MySql version? This question should probably be submitted to one of the MySql lists, but I'll help you if I can. Randy Clamons Systems Programming Astro-auction.com > ------------Original Message------------ > From: "Anne Shroeder" <anne@xxxxxxxxxxxxxxxxxx> > To: "PHP Windows Mailing List" <PHP-windows@xxxxxxxxxxxxx> > Date: Mon, Jan-10-2005 9:18 AM > Subject: SQL syntax question > > Not sure if this is the right list to post this to or not -- I'm having > a > problem with getting a MySQL query to work. I need to do two things: > > (1) multiply the value of x*y (and thus have to do a group by) from > fields > in one table, AND > (2) do a lookup of the name value in another table for a foreign key > from > the first table. > > E.g. > > select sum (qty1*unitprice1), field1, field2 > from table1 > group by field1, field2 > > That part works fine, but when I try to mix in the second table, it > breaks: > > select sum (table1.qty1*table1.unitprice1), table1.field1, > table1.field2, > table2.name > from table1, table2 > where table1.nameid = table2.id > group by table1.field1, table1.field2, table2.name > > Clearly, you can't group by a foreign table - but then how to do the > join > to get the name definition from table2? > > Anne > > -- > PHP Windows Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
