The USING operator is when the column is being used for the Join. Here, the OP just wanted to look up the same values in both tables, not join the results. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Neil Smith [MVP, Digital media] wrote: > >> Message-ID: <D6.0A.58592.9F97EC84@xxxxxxxxxxxx> >> To: php-db@xxxxxxxxxxxxx >> Reply-To: "Mike Sullivan" <mjsulliv@xxxxxxx> >> From: "Mike Sullivan" <mjsulliv@xxxxxxx> >> Date: Mon, 15 Sep 2008 11:06:30 -0400 >> Subject: Building WHERE SQL clauses >> >> Hello all. I'm using PHP to build a query for a database that >> consists of >> multiple tables, all with identical attribues. A typical syntax try >> looks >> like this: SELECT * FROM chico, harpo WHERE operator = "Bill" OR >> operator = >> "Jessica" >> >> MySQL responds with this: Couldn't execute query.Column 'operator' >> in where >> clause is ambiguous > > > That's right. There's no way to distinguish between the column names > where they're identical between 2 tables, so often you would prefix > those with the table name such as (assuming you want to use harpo as > the canonical source) : > > SELECT * FROM chico > LEFT JOIN harpo > ON `chico`.operator = `harpo`.operator > WHERE ( > `harpo`.operator = "Bill" OR > `harpo`.operator = "Jessica" > ); > > However SQL also has a `USING` clause which can be used where columns > really are identical : > > http://dev.mysql.com/doc/refman/5.0/en/join.html > "The USING(column_list) clause names a list of columns that must exist > in both tables" > > So you should also be able to do > > SELECT * FROM chico > LEFT JOIN harpo USING (operator) > WHERE ( > `harpo`.operator = "Bill" OR > `harpo`.operator = "Jessica" > ); > > > In that case you don't need to explicitly name the tables in the > USING() clause because the query parser notices that both tables have > columns named the same > > HTH > Cheers - Neil > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
