Re: [PHP-DB] Building WHERE SQL clauses
"Chris" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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
Re: [PHP-DB] 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: [PHP-DB] 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"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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.
Re: [PHP-DB] 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"" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
> [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: Re: [PHP-DB] Building WHERE SQL clauses
At 03:46 16/09/2008, you wrote: Message-ID: <[EMAIL PROTECTED]> Date: Mon, 15 Sep 2008 18:33:04 +0100 From: "Stephen Wellington" <[EMAIL PROTECTED]> To: "Mike Sullivan" <[EMAIL PROTECTED]>, 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: [PHP-DB] 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: [PHP-DB] Building WHERE SQL clauses
On Mon, Sep 15, 2008 at 1:33 PM, Stephen Wellington <
[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
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" 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 <[EMAIL PROTECTED]> 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 28 ext 12438 [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
