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

I was hoping that since the tables are identical all I would need to do is
list the attribute values not have to append them to the table names. Is
there any way to do this? Perhaps with a setting in MySQL or a different
syntax (JOIN, UNION, ...)? If not are there available some canned code
snippets that build these types of strings from values passed in the $_POST
array. Thanks for any insights on this. --- Mike



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Building WHERE SQL clauses

You probably want something like this:

SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR c.operator =
"Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"

However if those tables really are identical I would suggest having a
good look at your
database design to see if it can be normalised or something...

Stephen Wellington

On Mon, Sep 15, 2008 at 4:06 PM, Mike Sullivan wrote:
> 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
>
> I was hoping that since the tables are identical all I would need to do is
> list the attribute values not have to append them to the table names. Is
> there any way to do this? Perhaps with a setting in MySQL or a different
> syntax (JOIN, UNION, ...)? If not are there available some canned code
> snippets that build these types of strings from values passed in the $_POST
> array. Thanks for any insights on this. --- Mike
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>



--
Stephen Wellington
07956 042387
01865 280000 ext 12438
wellingtonsteve@gmail.com
steve@sjwwebdesign.co.uk
stephen.wellington@worc.ox.ac.uk

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Building WHERE SQL clauses

------=_Part_611_29080821.1221533173955
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
wellingtonsteve@gmail.com> wrote:

> You probably want something like this:
>
> SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR
> c.operator =
> "Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"
>
> However if those tables really are identical I would suggest having a
> good look at your
> database design to see if it can be normalised or something...
>
> Stephen Wellington
>
> On Mon, Sep 15, 2008 at 4:06 PM, Mike Sullivan wrote:
> > 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
> >
> > I was hoping that since the tables are identical all I would need to do
> is
> > list the attribute values not have to append them to the table names. Is
> > there any way to do this? Perhaps with a setting in MySQL or a different
> > syntax (JOIN, UNION, ...)? If not are there available some canned code
> > snippets that build these types of strings from values passed in the
> $_POST
> > array. Thanks for any insights on this. --- Mike
> >
> >
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
> if the structures and fields are truly the same you can UNION the queries

select * from chico where operator in('Jessica','William')
union
select * from harpo where operator in('Jessica','William')

But as suggested, if they are truly similar, the db needs to be looked for
design

--

Bastien

Cat, the other other white meat

------=_Part_611_29080821.1221533173955--

Re: Building WHERE SQL clauses

how about type like this

SELECT * FROM chico c, harpo h WHERE c.operator = "Bill" OR c.operator =
"Jessica"



Mike Sullivan wrote:
> 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
>
> I was hoping that since the tables are identical all I would need to do is
> list the attribute values not have to append them to the table names. Is
> there any way to do this? Perhaps with a setting in MySQL or a different
> syntax (JOIN, UNION, ...)? If not are there available some canned code
> snippets that build these types of strings from values passed in the $_POST
> array. Thanks for any insights on this. --- Mike
>
>
>
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Building WHERE SQL clauses

>Message-ID:
>To: php-db@lists.php.net
>Reply-To: "Mike Sullivan"
>From: "Mike Sullivan"
>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

Re: Re: Building WHERE SQL clauses

At 03:46 16/09/2008, you wrote:
>Message-ID: <29c0d3a50809151033o45c17c0aj7af050ae4498662@mail.gmail.com>
>Date: Mon, 15 Sep 2008 18:33:04 +0100
>From: "Stephen Wellington"
>To: "Mike Sullivan" , php-db@lists.php.net
>MIME-Version: 1.0
>Content-Type: text/plain; charset=UTF-8
>Content-Transfer-Encoding: 7bit
>Subject: Re: [PHP-DB] Building WHERE SQL clauses
>
>You probably want something like this:
>
>SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR c.operator =
>"Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"


With all due respect, I think that's going to be a cause of major
pain. You should try it out with the example table structures below.
I've added a PK which doesn't ovelap, so you can see the result
columns from both tables numerically

It's going to end up doing a FULL JOIN on each row from chico which
matches one of the 2 conditions, against the rows in harpo, which is
definitely the wrong result here. I'm assuming the OP wants at most
one row from either table which contains the other values from the
table matching the result filter.

On later consideration (he didn't make clear what the desired result
was) it could well be the UNION result he's after, if both tables
really are identical in structure, as mentioned by the later message
(unless that was yours ;-)


CREATE TABLE `chico` (
`item` smallint(6) NOT NULL AUTO_INCREMENT,
`operator` char(32) NOT NULL,
PRIMARY KEY (`item`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- Duplicate chico table structure exactly
CREATE TABLE `harpo` LIKE `chico`;


INSERT INTO `chico` (`item`, `operator`) VALUES (1, 'Bill'), (2, 'Jessica'),
(3, 'Dave'), (4, 'Clara'), (5, 'Ally'), (6, 'Josh'), (9, 'Mark'),
(10, 'Sophie');

INSERT INTO `harpo` (`item`, `operator`) VALUES (13, 'Mark'), (14,
'Sophie'), (15, 'Bill'), (16, 'Jessica');



HTH
Cheers - Neil





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Building WHERE SQL clauses

Hi all thanks for the responses! What I have is a 6 table db that has each

table created from the output of 6 identical laboratory machines (chico,

harpo, ...). The out put is a text file which I import as a table named

after the machine. I do realize that one solution is to add the machine

name as a attribute and concatenate the tables together. I'm going to weigh

that change against Bastien's suggestion of UNIONing the selects.

I must admit that I'm still rather weak in the SQL department and did try a

solution of:

SELECT * FROM (chico UNION harpo) WHERE operator = "Bill" OR operator =

"Jessica" but that apparently is a SQL syntax error.

Thanks again for the insight and any other suggestions you might have. ---
Mike



""Bastien Koert"" wrote in message
news:d7b6cab70809151946h42b626cfr37496eb2583b863a@mail.gmail .com...
> On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
> wellingtonsteve@gmail.com> wrote:
>
>> You probably want something like this:
>>
>> SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR
>> c.operator =
>> "Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"
>>
>> However if those tables really are identical I would suggest having a
>> good look at your
>> database design to see if it can be normalised or something...
>>
>> Stephen Wellington
>>
>> On Mon, Sep 15, 2008 at 4:06 PM, Mike Sullivan wrote:
>> > 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
>> >
>> > I was hoping that since the tables are identical all I would need to do
>> is
>> > list the attribute values not have to append them to the table names.
>> > Is
>> > there any way to do this? Perhaps with a setting in MySQL or a
>> > different
>> > syntax (JOIN, UNION, ...)? If not are there available some canned code
>> > snippets that build these types of strings from values passed in the
>> $_POST
>> > array. Thanks for any insights on this. --- Mike
>> >
>> >
>> >
>> > --
>> > PHP Database Mailing List (http://www.php.net/)
>> > To unsubscribe, visit: http://www.php.net/unsub.php
>> >
>> >
>>
>> if the structures and fields are truly the same you can UNION the queries
>
> select * from chico where operator in('Jessica','William')
> union
> select * from harpo where operator in('Jessica','William')
>
> But as suggested, if they are truly similar, the db needs to be looked for
> design
>
> --
>
> Bastien
>
> Cat, the other other white meat
>



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Building WHERE SQL clauses

--------------090701000502050500090104
Content-Type: text/plain; charset=ISO-8859-7; format=flowed
Content-Transfer-Encoding: 7bit


> Hi all thanks for the responses! What I have is a 6 table db that has each
>
> table created from the output of 6 identical laboratory machines (chico,
>
> harpo, ...). The out put is a text file which I import as a table named
>
> after the machine. I do realize that one solution is to add the machine
>
> name as a attribute and concatenate the tables together. I'm going to weigh
>
> that change against Bastien's suggestion of UNIONing the selects.
>
> I must admit that I'm still rather weak in the SQL department and did try a
>
> solution of:
>
> SELECT * FROM (chico UNION harpo) WHERE operator = "Bill" OR operator =
>
> "Jessica" but that apparently is a SQL syntax error.
>
> Thanks again for the insight and any other suggestions you might have. ---
> Mike
>
>
>
> ""Bastien Koert"" wrote in message
> news:d7b6cab70809151946h42b626cfr37496eb2583b863a@mail.gmail .com...
>
>> On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
>> wellingtonsteve@gmail.com> wrote:
>>
>>
>>> You probably want something like this:
>>>
>>> SELECT * FROM chico as c, harpo as h WHERE c.operator = "Bill" OR
>>> c.operator =
>>> "Jessica" OR h.operator = "Bill" OR h.operator ="Jessica"
>>>
>>> However if those tables really are identical I would suggest having a
>>> good look at your
>>> database design to see if it can be normalised or something...
>>>
>>> Stephen Wellington
>>>
>>> On Mon, Sep 15, 2008 at 4:06 PM, Mike Sullivan wrote:
>>>
>>>> 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
>>>>
>>>> I was hoping that since the tables are identical all I would need to do
>>>>
>>> is
>>>
>>>> list the attribute values not have to append them to the table names.
>>>> Is
>>>> there any way to do this? Perhaps with a setting in MySQL or a
>>>> different
>>>> syntax (JOIN, UNION, ...)? If not are there available some canned code
>>>> snippets that build these types of strings from values passed in the
>>>>
>>> $_POST
>>>
>>>> array. Thanks for any insights on this. --- Mike
>>>>
>>>>
>>>>
>>>> --
>>>> PHP Database Mailing List (http://www.php.net/)
>>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>>
>>>>
>>>>
>>> if the structures and fields are truly the same you can UNION the queries
>>>
>> select * from chico where operator in('Jessica','William')
>> union
>> select * from harpo where operator in('Jessica','William')
>>
>> But as suggested, if they are truly similar, the db needs to be looked for
>> design
>>
>> --
>>
>> Bastien
>>
>> Cat, the other other white meat
>>
>>
>
>
>
>

Well this is the best idea you had up to now !! It will resolve the
complexity and it will normalize the tables. Not to mention that if you
add extra machines you will have to change the database schema and
probably you will need to rewrite the script. So since changing the
schema now and then is not recommended at least by mysql this is what I
suggest.

--------------090701000502050500090104--

Re: Re: Building WHERE SQL clauses

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:
>> To: php-db@lists.php.net
>> Reply-To: "Mike Sullivan"
>> From: "Mike Sullivan"
>> 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

Re: Building WHERE SQL clauses

Mike Sullivan wrote:
> Hi all thanks for the responses! What I have is a 6 table db that has each
>
> table created from the output of 6 identical laboratory machines (chico,
>
> harpo, ...). The out put is a text file which I import as a table named
>
> after the machine. I do realize that one solution is to add the machine
>
> name as a attribute and concatenate the tables together. I'm going to weigh
>
> that change against Bastien's suggestion of UNIONing the selects.
>
> I must admit that I'm still rather weak in the SQL department and did try a
>
> solution of:
>
> SELECT * FROM (chico UNION harpo) WHERE operator = "Bill" OR operator =
>
> "Jessica" but that apparently is a SQL syntax error.
>
> Thanks again for the insight and any other suggestions you might have. ---
> Mike

A UNION combines the results from query 1 and query 2 together.

For example:

select * from chico where operator in ('Bill', 'Jessica')
union
select * from harpo in ('Bill', 'Jessica')

will:

- get all rows from the chico table where the operator is Bill or Jessica
- get all rows from the harpo table where the operator is Bill or Jessica
- remove duplicate results
- return the results


A UNION ALL will skip the 'remove duplicates' step:

- get all rows from the chico table where the operator is Bill or Jessica
- get all rows from the harpo table where the operator is Bill or Jessica
- return the results

If query 1 AND query 2 don't return the results you want, then a union
is the wrong type of query to run.

http://dev.mysql.com/doc/refman/5.0/en/union.html

I'm confused about what you're trying to get out of the results, can you
explain further and give an example of the data you have, and the result
you want to return?

--
Postgresql & php tutorials
http://www.designmagick.com/



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Building WHERE SQL clauses

"Chris" wrote in message
news:48D0352A.9060708@gmail.com...
> Mike Sullivan wrote:
>> Hi all thanks for the responses! What I have is a 6 table db that has
>> each
>>
>> table created from the output of 6 identical laboratory machines (chico,
>>
>> harpo, ...). The out put is a text file which I import as a table named
>>
>> after the machine. I do realize that one solution is to add the machine
>>
>> name as a attribute and concatenate the tables together. I'm going to
>> weigh
>>
>> that change against Bastien's suggestion of UNIONing the selects.
>>
>> I must admit that I'm still rather weak in the SQL department and did try
>> a
>>
>> solution of:
>>
>> SELECT * FROM (chico UNION harpo) WHERE operator = "Bill" OR operator =
>>
>> "Jessica" but that apparently is a SQL syntax error.
>>
>> Thanks again for the insight and any other suggestions you might
>> have. --- Mike
>
> A UNION combines the results from query 1 and query 2 together.
>
> For example:
>
> select * from chico where operator in ('Bill', 'Jessica')
> union
> select * from harpo in ('Bill', 'Jessica')
>
> will:
>
> - get all rows from the chico table where the operator is Bill or Jessica
> - get all rows from the harpo table where the operator is Bill or Jessica
> - remove duplicate results
> - return the results
>
>
> A UNION ALL will skip the 'remove duplicates' step:
>
> - get all rows from the chico table where the operator is Bill or Jessica
> - get all rows from the harpo table where the operator is Bill or Jessica
> - return the results
>
> If query 1 AND query 2 don't return the results you want, then a union
> is the wrong type of query to run.
>
> http://dev.mysql.com/doc/refman/5.0/en/union.html
>
> I'm confused about what you're trying to get out of the results, can you
> explain further and give an example of the data you have, and the result
> you want to return?
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>

From what I've experimented around with from the suggestions gathered here,
a union is what I want. and had tried that but using table names as the
parameters to UNION not the results of query's as you show above. I've got
a prototype like this now working but I've pretty much decided to go back
and redo the java program that parses the text files and builds the
LOAD-able files so that it adds a machine name attribute and concatenates
the data in one file.

--- Mike



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php