RE: Where exists query syntax error?

[SGreen]

"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 01:07:00 PM:

> I apologize for not providing more information.  I am currently 
> using 4.1.8 client and server on windows xp.
> 
> 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 02, 2005 11:48 AM
> To: Barbara Deaton
> Cc: mysql@lists.mysql.com
> Subject: Re: Where exists query syntax error?
> 
> 
> 
> 
> "Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 
AM:
> 
> > I need help re-writing a query.
> > 
> > When accessing DB2 I can execute the below:
> > delete from DM_KOHLS.clr_plan_price_change_metrics A  
> > where A.start_dt>= Date( '05/15/2005' ) and  
> >   exists ( select * from SESSION.delete_table_tmp 
> B 
> >where A.clr_plan_sk = B.clr_plan_sk) 
> > 
> > 
> > I modified it to be what I thought was a valid MySQL statement but 
> > when I execute it against MySQL I get a syntax error:
> > 
> > ERROR: Execute error: You have an error in your SQL syntax.  Check 
> > the manual that corresponds
> >to your MySQL server version for the right syntax to use near
> > 'exists ( select * from
> >delete_table_tmp where clr_plan_price_ch
> > 
> > 
> > The statement I executed is:
> > delete from clr_plan_price_change_metrics 
> > where start_dt>= '2005-05-15' and 
> >   exists ( select * from delete_table_tmp 
> >where clr_plan_price_change_metrics.
> > clr_plan_sk = delete_table_tmp.clr_plan_sk)
> > 
> > 
> > I can successfully execute each piece of the query, but when I 
> > combine it with the 'exists' subquery it fails.
> > 
> > The examples in the doc show just selects... Exists() so I tried 
> > changing it to a select from table and still fails.  What is the 
> > correct syntax?
> > -Barb.
> > 
> 
> Your version, depending on how old it is, may not support the EXISTS
> predicate. It may not support the subquery. It may not even support 
> deletes involving more than one table at a time. Please tell us 
> which version you are using so that we can give you a form of your 
> DELETE query compatible with your server. 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine


According to http://dev.mysql.com/doc/mysql/en/delete.html there are 3 
valid forms of the DELETE statement. You seem to be trying to use the 
first one listed

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_definition]
   [ORDER BY ...]
   [LIMIT row_count]

delete from clr_plan_price_change_metrics 
where start_dt>= '2005-05-15' 
and exists ( 
select * 
from delete_table_tmp 
where clr_plan_price_change_metrics.clr_plan_sk = 
delete_table_tmp.clr_plan_sk
)

This is documented to work for SELECT queries but I am not 100% certain it 
works in DELETE queries. I looked for a bug on this. I seem to remember 
seeing a change log entry saying something like this was fixed but my 
boolean query keeps timing out so I can not reference the change log entry 
or what version it was fixed in. Sorry.

One way to actually do what you want to do is to NOT use a subquery and 
use either one of the "multi-table" forms. I prefer the first

DELETE clr_plan_price_change_metrics
FROM clr_plan_price_change_metrics
INNER JOIN delete_table_tmp
ON clr_plan_price_change_metrics.clr_plan_sk = 
delete_table_tmp.clr_plan_sk
WHERE start_dt>= '2005-05-15' 


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 

RE: Where exists query syntax error?

[Barbara Deaton]

I apologize for not providing more information.  I am currently using 4.1.8 
client and server on windows xp.



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 02, 2005 11:48 AM
To: Barbara Deaton
Cc: mysql@lists.mysql.com
Subject: Re: Where exists query syntax error?




"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 AM:

> I need help re-writing a query.
> 
> When accessing DB2 I can execute the below:
> delete from DM_KOHLS.clr_plan_price_change_metrics A 
> where A.start_dt>= Date( '05/15/2005' ) and  
>   exists ( select * from SESSION.delete_table_tmp B  
>where A.clr_plan_sk = B.clr_plan_sk) 
> 
> 
> I modified it to be what I thought was a valid MySQL statement but 
> when I execute it against MySQL I get a syntax error:
> 
> ERROR: Execute error: You have an error in your SQL syntax.  Check 
> the manual that corresponds
>to your MySQL server version for the right syntax to use near
> 'exists ( select * from
>delete_table_tmp where clr_plan_price_ch
> 
> 
> The statement I executed is:
> delete from clr_plan_price_change_metrics  
> where start_dt>= '2005-05-15' and 
>   exists ( select * from delete_table_tmp 
>where clr_plan_price_change_metrics.
> clr_plan_sk = delete_table_tmp.clr_plan_sk)
> 
> 
> I can successfully execute each piece of the query, but when I 
> combine it with the 'exists' subquery it fails.
> 
> The examples in the doc show just selects... Exists() so I tried 
> changing it to a select from table and still fails.  What is the 
> correct syntax?
> -Barb.
> 

Your version, depending on how old it is, may not support the EXISTS predicate. 
It may not support the subquery. It may not even support deletes involving more 
than one table at a time. Please tell us which version you are using so that we 
can give you a form of your DELETE query compatible with your server. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Where exists query syntax error?

[SGreen]

"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 AM:

> I need help re-writing a query.
> 
> When accessing DB2 I can execute the below:
> delete from DM_KOHLS.clr_plan_price_change_metrics A  
> where A.start_dt>= Date( '05/15/2005' ) and  
>   exists ( select * from SESSION.delete_table_tmp B  
>where A.clr_plan_sk = B.clr_plan_sk) 
> 
> 
> I modified it to be what I thought was a valid MySQL statement but 
> when I execute it against MySQL I get a syntax error:
> 
> ERROR: Execute error: You have an error in your SQL syntax.  Check 
> the manual that corresponds
>to your MySQL server version for the right syntax to use near
> 'exists ( select * from
>delete_table_tmp where clr_plan_price_ch
> 
> 
> The statement I executed is:
> delete from clr_plan_price_change_metrics 
> where start_dt>= '2005-05-15' and 
>   exists ( select * from delete_table_tmp 
>where clr_plan_price_change_metrics.
> clr_plan_sk = delete_table_tmp.clr_plan_sk)
> 
> 
> I can successfully execute each piece of the query, but when I 
> combine it with the 'exists' subquery it fails.
> 
> The examples in the doc show just selects... Exists() so I tried 
> changing it to a select from table and still fails.  What is the 
> correct syntax?
> -Barb.
> 

Your version, depending on how old it is, may not support the EXISTS 
predicate. It may not support the subquery. It may not even support 
deletes involving more than one table at a time. Please tell us which 
version you are using so that we can give you a form of your DELETE query 
compatible with your server.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Where exists query syntax error?

[Barbara Deaton]

I need help re-writing a query.

When accessing DB2 I can execute the below:
delete from DM_KOHLS.clr_plan_price_change_metrics A
   
where A.start_dt>= Date( '05/15/2005' ) and 

  exists ( select * from SESSION.delete_table_tmp B 

   where A.clr_plan_sk = B.clr_plan_sk) 


I modified it to be what I thought was a valid MySQL statement but when I 
execute it against MySQL I get a syntax error:

ERROR: Execute error: You have an error in your SQL syntax.  Check the manual 
that corresponds
   to your MySQL server version for the right syntax to use near 'exists ( 
select * from
   delete_table_tmp where clr_plan_price_ch


The statement I executed is:
delete from clr_plan_price_change_metrics  
where start_dt>= '2005-05-15' and 
  exists ( select * from delete_table_tmp 
   where clr_plan_price_change_metrics.clr_plan_sk = 
delete_table_tmp.clr_plan_sk)


I can successfully execute each piece of the query, but when I combine it with 
the 'exists' subquery it fails.

The examples in the doc show just selects... Exists() so I tried changing it to 
a select from table and still fails.  What is the correct syntax?
-Barb.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

query syntax in new mysql query browser

[Eben]

I have a sql script that issues a bunch of truncate statements to purge 
a database in development.  The script looks like:

truncate table table_1;
truncate table tabel_2;
truncate table table_3;
and so on...
I can load this script as a single query in the old mysql_front (version 
2.2) GUI and it runs fine.  However the same query loaded into MySQL 
Query Browser fails after the first line.  Is there a way to structure 
the syntax differently so I can run the multiple truncates in one query 
via Qeury Browser?

Any advice is appreciated,
Eben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Correct date query syntax

[SGreen]

I believe you are not letting MySQL do enough work for you.  The date 
format is perfect, even as a string, to perform the comparison you are 
trying to perform.

SELECT @currTime := NOW();
+-+
| @currTime := NOW()  |
+-+
| 2004-08-26 12:48:16 |
+-+
1 row in set (0.00 sec)

SELECT @currTime as CurrentTime
, (@currTime <= '2005-08-01')
, (@currTime > '2005-08-01')
, (@currTime > '2004-08-26 12:00:00')\G
*** 1. row ***
CurrentTime: 2004-08-26 12:48:16
(@currTime <= '2005-08-01'): 1
 (@currTime > '2005-08-01'): 0
(@currTime > '2004-08-26 12:00:00'): 1
1 row in set (0.00 sec)

As you can see, MySQL is perfectly capable of comparing dates and 
datetimes if you just let it. There is no reason to convert them to any 
sort of intermediate value as it will do it for you internally and 
automatically. Please read this to see just how many different ways Dates 
can be specified:

http://dev.mysql.com/doc/mysql/en/DATETIME.html



You could try this as your query:

SET @date1 ='2004-08-12', date2='2004-08-18';

SELECT * 
FROM account 
WHERE (date >= date1) 
AND (date <= date2);


and it should work just fine.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
 

"Yong Wang" <[EMAIL PROTECTED]> wrote on 08/26/2004 12:31:46 PM:

> Hi, All:
> I have a database which contains date attribute in string format
> (like 2004-08-12). I want to genearte a report based on period time.
> I use the syntax:
> date1 ='2004-08-12'
> date2='2004-08-18'
> SELECT * FROM account WHERE (TO_DAYS(date) >= TODAYS(date1)) and
> (TO_DAYS(date) <= TO_DAYS(date2));
> The report script complains the condition after WHERE clause. The
> reason I use TO_DAYS is that I want to convert 
> string date data into integer for comparison. Can I use TO_DAYS() like
> this way ?
>  Thanks a lot.
> 
> Yong
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Correct date query syntax

[Jochem van Dieten]

On Thu, 26 Aug 2004 11:31:46 -0500, Yong Wang <[EMAIL PROTECTED]> wrote:
> 
> I have a database which contains date attribute in string format
> (like 2004-08-12). I want to genearte a report based on period time.
> I use the syntax:
> date1 ='2004-08-12'
> date2='2004-08-18'
> SELECT * FROM account WHERE (TO_DAYS(date) >= TODAYS(date1)) and
> (TO_DAYS(date) <= TO_DAYS(date2));

Is "date" the name of your field? Change it, it is a reserved word in SQL.


> The report script complains the condition after WHERE clause. The
> reason I use TO_DAYS is that I want to convert
> string date data into integer for comparison. Can I use TO_DAYS() like
> this way ?


Even if you can, don't: it isn't needed.
Just use a plain BETWEEN predicate without functions:
SELECT *
FROM account
WHERE date BETWEEN date1 AND date2

Jochem

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Correct date query syntax

[Yong Wang]

Hi, All:
I have a database which contains date attribute in string format
(like 2004-08-12). I want to genearte a report based on period time.
I use the syntax:
date1 ='2004-08-12'
date2='2004-08-18'
SELECT * FROM account WHERE (TO_DAYS(date) >= TODAYS(date1)) and
(TO_DAYS(date) <= TO_DAYS(date2));
The report script complains the condition after WHERE clause. The
reason I use TO_DAYS is that I want to convert 
string date data into integer for comparison. Can I use TO_DAYS() like
this way ?
 Thanks a lot.

Yong


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query syntax help

[Mike Blezien]

Thx's Fred...

as soon as I sent the email and re-read it again... I spotted the 'as' alias 
table reference to the table, was actual a reserved word,..causing the error :)

thx's again.

--
MikeBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Fred van Engen wrote:
On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote:

I've been looking at this SQL query a dozen times or more, but keep getting 
a syntax error message, Query:

SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS 
name,aw.siteid,ai.email,as.username,as.status
FROM affiliate_info ai,affiliate_signup as,affiliate_website aw


AS is a reserved word.


WHERE aw.siteid = 1000
AND ai.affilid = as.affilid AND aw.affilid = ai.affilid
what is wrong with this query syntax ?? the syntax error is suppose to be 
in this area:
`affiliate_website aw WHERE aw.siteid = 1000`



A bit before that.

Regards,

Fred.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query syntax help

[Jochem van Dieten]

Mike Blezien said:
>
> I've been looking at this SQL query a dozen times or more, but keep
> getting a  syntax error message, Query:
>
> SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS
> name,aw.siteid,ai.email,as.username,as.status
> FROM affiliate_info ai,affiliate_signup as,affiliate_website aw
  ^^
 reserved word

> WHERE aw.siteid = 1000
> AND ai.affilid = as.affilid AND aw.affilid = ai.affilid

Jochem





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query syntax help

[Fred van Engen]

On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote:
> I've been looking at this SQL query a dozen times or more, but keep getting 
> a syntax error message, Query:
> 
> SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS 
> name,aw.siteid,ai.email,as.username,as.status
> FROM affiliate_info ai,affiliate_signup as,affiliate_website aw

AS is a reserved word.

> WHERE aw.siteid = 1000
> AND ai.affilid = as.affilid AND aw.affilid = ai.affilid
> 
> what is wrong with this query syntax ?? the syntax error is suppose to be 
> in this area:
> `affiliate_website aw WHERE aw.siteid = 1000`
> 

A bit before that.


Regards,

Fred.

-- 
Fred van Engen  XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC  Almere
fax: +31 36 5462424 The Netherlands

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

query syntax help

[Mike Blezien]

Hello all,

I've been looking at this SQL query a dozen times or more, but keep getting a 
syntax error message, Query:

SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS 
name,aw.siteid,ai.email,as.username,as.status
FROM affiliate_info ai,affiliate_signup as,affiliate_website aw
WHERE aw.siteid = 1000
AND ai.affilid = as.affilid AND aw.affilid = ai.affilid

what is wrong with this query syntax ?? the syntax error is suppose to be in 
this area:
`affiliate_website aw WHERE aw.siteid = 1000`

MySQL version 4.0.15 w/InnoDB tables

TIA,

--
MikeBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[Fwd: Re: Query syntax.]

[Michael Stassen]

The mysql list sent this back to me, so I'm resending.  My apologies to 
anyone who ends up getting it twice.

Data Boy wrote:

Will and Michael,

Thanks very much for the the replies. This works well.
Is it possible to use this syntax and search for cases
where they have two different kinds of equipment? Say
an Ultra 5 and a HP Plotter?
TIA, DB


SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
WHERE a.User_Account = b.Device_Account
AND b.Device_Name LIKE  'HP%'

You're welcome.

On second look, I realize we didn't even need a join for your orginal
question as you only wanted the account ID.  Since Users.User_Account =
Devices.Device_Account, we could simply have done
SELECT Device_Account AS User_Account FROM Devices
WHERE Device_Name LIKE  'HP%'
Of course, usually you would want some corresponding information from
the Users table, such as the User_Name.  Then you need the join I suggested.
I know of two ways to search for users with a specified list of equipment:

1) You can join the Devices table with itself on Device_Account, looking
for results where the left copy matches the first piece of equipment and
the right copy matches the other piece of equipment.  You can also join
to the Users table to look up user info at the same time.  Something
like this:
SELECT User_Account AS ID, User_Name AS Name
FROM Users AS u, Devices AS d1, Devices AS d2
WHERE u.User_Account = d1.Device_Account
AND d1.Device_Account = d2.Device_Account
AND d1.Device_Name = 'HP Plotter'
AND d2.Device_Name = 'Ultra 5'
You replace the last two lines according to your desired equipment
search.  I like this conceptually -- it's simply an extension of the
join we already did -- but it doesn't generalize very well to longer
lists of equipment.
2) You can use some aggregation functions to get what you want.
Something like this:
SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN ('HP Plotter', 'Ultra 5')
GROUP BY account
HAVING COUNT(*) = 2
This version generalizes nicely:

SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN (List_of_Equipment)
GROUP BY account
HAVING COUNT(*) = Number_of_items_in_list
You replace "List_of_Equipment" and "Number_of_items_in_list" with
appropriate values, of course..
Michael





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query syntax.

[Michael Stassen]

Data Boy wrote:

Will and Michael,

Thanks very much for the the replies. This works well.
Is it possible to use this syntax and search for cases
where they have two different kinds of equipment? Say
an Ultra 5 and a HP Plotter?
TIA, DB


SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
WHERE a.User_Account = b.Device_Account
AND b.Device_Name LIKE  'HP%'

You're welcome.

On second look, I realize we didn't even need a join for your orginal 
question as you only wanted the account ID.  Since Users.User_Account = 
Devices.Device_Account, we could simply have done

SELECT Device_Account AS User_Account FROM Devices
WHERE Device_Name LIKE  'HP%'
Of course, usually you would want some corresponding information from 
the Users table, such as the User_Name.  Then you need the join I suggested.

I know of two ways to search for users with a specified list of equipment:

1) You can join the Devices table with itself on Device_Account, looking 
for results where the left copy matches the first piece of equipment and 
the right copy matches the other piece of equipment.  You can also join 
to the Users table to look up user info at the same time.  Something 
like this:

SELECT User_Account AS ID, User_Name AS Name
FROM Users AS u, Devices AS d1, Devices AS d2
WHERE u.User_Account = d1.Device_Account
AND d1.Device_Account = d2.Device_Account
AND d1.Device_Name = 'HP Plotter'
AND d2.Device_Name = 'Ultra 5'
You replace the last two lines according to your desired equipment 
search.  I like this conceptually -- it's simply an extension of the 
join we already did -- but it doesn't generalize very well to longer 
lists of equipment.

2) You can use some aggregation functions to get what you want. 
Something like this:

SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN ('HP Plotter', 'Ultra 5')
GROUP BY account
HAVING COUNT(*) = 2
This version generalizes nicely:

SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN (List_of_Equipment)
GROUP BY account
HAVING COUNT(*) = Number_of_items_in_list
You replace "List_of_Equipment" and "Number_of_items_in_list" with 
appropriate values, of course..

Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query syntax.

[Data Boy]

Will and Michael,

Thanks very much for the the replies. This works well.
Is it possible to use this syntax and search for cases
where they have two different kinds of equipment? Say
an Ultra 5 and a HP Plotter?

TIA, DB

> SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
> WHERE a.User_Account = b.Device_Account
> AND b.Device_Name LIKE  'HP%'



- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Will Lowe" <[EMAIL PROTECTED]>
Cc: "Data Boy" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, December 22, 2003 1:25 PM
Subject: Re: Query syntax.


>
> Will Lowe wrote:
>
> >>Select User_Account from Users as a, Devices as b
> >>WHERE
> >> a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices
> >>   WHERE b.Device_Name LIKE  'HP%' )
> >
> >
> >> I'm running 3.23.49 which I know is not the most current..it was
installed
> >
> >
> > 3.x does not support subselects ("select x from (select y from ...)").
> > You'll need to upgrade to 4.1.
> >
>
> But 4.1 is alpha, so he may not want to do that (though it would be a
> good idea to upgrade to 3.23.58 or 4.0.17).  In that case, the solution
> is to replace the subselect with a join, which may even be more
> efficient.  Try:
>
> SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
> WHERE a.User_Account = b.Device_Account
> AND b.Device_Name LIKE  'HP%'
>
> See <http://www.mysql.com/doc/en/Rewriting_subqueries.html> for more.
>
> Michael
>
>

Hi,

 I'm having problems with the syntax of a select statement. I have two
tables linked
 together by account number.

 The first table (Users) looks similar to this

|User_Account | User_Name | User_Address
|X10010100110  | Michael Smith  | 1000 North Main St
|X10010100240  |  David Wilson  | 1200 State Street

The second table (Devices) looks like this

|Device_Account  |  Device_Name| Installaton_Date
|X10010100240  |  Ultra 5| 19981010
|X10010100240  |  HP1055CM   | 20010528
|X10010100240  |  LEXMARK   | 20010529
|X10010100110  |  HP1055CM   | 20010528
|X10010100211  |  HP LJET 4M | 20010528

There is a one to many relationship between users and devices and not all
users
have devices. I'd like to select a list of User accounts with certain
devices.

Select User_Account from Users as a, Devices as b
WHERE
 a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices
   WHERE b.Device_Name LIKE  'HP%' )

I get an error code

 Error Code:1064
 You have an error in your SQL Syntax near 'Select DISTINCT'.

 I'm running 3.23.49 which I know is not the most current..it was installed
 with another package. Thanks for any advice. I know my SQL skills
 are limited!

 TIA,  DB





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query syntax.

[Michael Stassen]

Will Lowe wrote:

Select User_Account from Users as a, Devices as b
WHERE
a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices
  WHERE b.Device_Name LIKE  'HP%' )


I'm running 3.23.49 which I know is not the most current..it was installed


3.x does not support subselects ("select x from (select y from ...)").
You'll need to upgrade to 4.1.
But 4.1 is alpha, so he may not want to do that (though it would be a 
good idea to upgrade to 3.23.58 or 4.0.17).  In that case, the solution 
is to replace the subselect with a join, which may even be more 
efficient.  Try:

SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
WHERE a.User_Account = b.Device_Account
AND b.Device_Name LIKE  'HP%'
See  for more.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query syntax.

[Will Lowe]

> Select User_Account from Users as a, Devices as b
> WHERE
>  a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices
>WHERE b.Device_Name LIKE  'HP%' )

>  I'm running 3.23.49 which I know is not the most current..it was installed

3.x does not support subselects ("select x from (select y from ...)").
You'll need to upgrade to 4.1.

-- 
thanks,

Will

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query syntax.

[Data Boy]

 Hi,

 I'm having problems with the syntax of a select statement. I have two
tables linked
 together by account number.

 The first table (Users) looks similar to this

|User_Account | User_Name | User_Address
|X10010100110  | Michael Smith  | 1000 North Main St
|X10010100240  |  David Wilson  | 1200 State Street

The second table (Devices) looks like this

|Device_Account  |  Device_Name| Installaton_Date
|X10010100240  |  Ultra 5| 19981010
|X10010100240  |  HP1055CM   | 20010528
|X10010100240  |  LEXMARK   | 20010529
|X10010100110  |  HP1055CM   | 20010528
|X10010100211  |  HP LJET 4M | 20010528

There is a one to many relationship between users and devices and not all
users
have devices. I'd like to select a list of User accounts with certain
devices.

Select User_Account from Users as a, Devices as b
WHERE
 a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices
   WHERE b.Device_Name LIKE  'HP%' )

I get an error code

 Error Code:1064
 You have an error in your SQL Syntax near 'Select DISTINCT'.

 I'm running 3.23.49 which I know is not the most current..it was installed
 with another package. Thanks for any advice. I know my SQL skills
 are limited!

 TIA,  DB





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select query syntax help

[Thomas Spahni]

Dan,

SELECT ResourceTable.* FROM ResourceTable
   LEFT JOIN ResourceLinkTable
  ON ResourceTable.ResourceID = ResourceLinkTable.ResourceID
   WHERE ResourceLinkTable.ResourceID IS NULL;

Regards,
Thomas


On Thu, 30 Oct 2003, Dan Lamb wrote:

> Hello All,
>
> I have two table the look like this (greatly simplified):
>
> ResourceTable
> -
> int ResourceID
> var ResourceName
>
> ResourceLinkTable
> -
> int ResourceLinkID
> int ResourceID
> var Text
>
> I need to find all rows in ResourceTable for which there is NO entry in
> ResourceLinkTable.  I know I could do this with sub-selects like this:
>
> Select * from ResourceTable where ResourceID not in (select distinct
> ResourceID from ResourceLinkTable)
>
> How can I do this in MySQL 4.0 without using sub-selects?
>
> Thanks,
> Dan Lamb
>
>
>
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: select query syntax help [ANSWER]

[Fortuno, Adam]

This is a common question. The syntax looks like this:

SELECT a.* 
  FROM tbl_a AS a LEFT JOIN tbl_b AS b 
ON a.id = b.id
 WHERE b.id.id IS NULL;

The idea is you're retrieving a recordset of the two tables where the rows
are joined on the id. For tbl_b, the id field has no value (its null) so you
can identify those rows by asking for nulls in the `tbl_b` `id` column.

In your case, I would try:

SELECT tbl.* 
  FROM ResourceTable AS tbl LEFT JOIN ResourceLinkTable AS lnk 
ON tbl.ResourceID= lnk.ResourceID
 WHERE lnk.ResourceID.id IS NULL
ORDER BY ResourceName ASC;

Regards,
Adam

-Original Message-
From: Dan Lamb [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 30, 2003 9:39 AM
To: [EMAIL PROTECTED]
Subject: select query syntax help


Hello All,

I have two table the look like this (greatly simplified):

ResourceTable
-
int ResourceID
var ResourceName

ResourceLinkTable
-
int ResourceLinkID
int ResourceID
var Text

I need to find all rows in ResourceTable for which there is NO entry in
ResourceLinkTable.  I know I could do this with sub-selects like this:

Select * from ResourceTable where ResourceID not in (select distinct
ResourceID from ResourceLinkTable) 

How can I do this in MySQL 4.0 without using sub-selects?

Thanks,
Dan Lamb




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

select query syntax help

[Dan Lamb]

Hello All,

I have two table the look like this (greatly simplified):

ResourceTable
-
int ResourceID
var ResourceName

ResourceLinkTable
-
int ResourceLinkID
int ResourceID
var Text

I need to find all rows in ResourceTable for which there is NO entry in
ResourceLinkTable.  I know I could do this with sub-selects like this:

Select * from ResourceTable where ResourceID not in (select distinct
ResourceID from ResourceLinkTable) 

How can I do this in MySQL 4.0 without using sub-selects?

Thanks,
Dan Lamb




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Query Syntax Error

[Tab Alleman]

Trevor Sather wrote:
> Hello
> 
> The following query used to work when I was using an Access database,
> but now that I've moved to MySQL I get a syntax error when I try and
> run it:  
> 
> SELECT *, (SELECT COUNT (*)
> FROM Links
> WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes')  AS
> LINK_COUNT  FROM Categories ORDER BY CAT_NAME ASC 
> 
> The error message is this:
> 
> SQLState: 42000
> Native Error Code: 1064
> [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE
> Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 
> 
> Any immediate reactions?  Thanks in advance for any help you can give
> me... 

What version are you using?  Sub-selects only became available in MySQL
4.1 (I think).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL Query Syntax Error

[Trevor Sather]

Hello

The following query used to work when I was using an Access database, but
now that I've moved to MySQL I get a syntax error when I try and run it:

SELECT *, (SELECT COUNT (*)
FROM Links
WHERE Links.CAT_ID = Categories.CAT_ID AND LINK_APPROVED = 'Yes')  AS
LINK_COUNT  FROM Categories
ORDER BY CAT_NAME ASC

The error message is this:

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE
Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1

Any immediate reactions?  Thanks in advance for any help you can give me...

Best wishes

Trevor Sather

Re: Query syntax help?

[Tore Bostrup]

Try:

SELECT FF.name AS thename,
   MAX(FF.label) AS thelabel,
   F.name AS fieldsname
FROM regformfields as FF
INNER JOIN regfields as F
ON (FF.name = F.Name)
WHERE FF.label != ''
GROUP BY FF.name, F.name

I don't think you can include the ORDER BY F.saveorder (another column) in
this case, unless you include it (F.saveorder) in the SELECT and GROUP BY
list.

HTH,
Tore.


- Original Message -
From: "Scott Brown" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, February 22, 2003 1:57 PM
Subject: Query syntax help?


> OK, I am having a bit of trouble designing a MySQL query that returns what
> I want. Here is the query as I have it thus far:
>
> SELECT DISTINCT regformfields.name AS thename,
>regformfields.label AS thelabel,
>regfields.name AS fieldsname
> FROM regformfields
> INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE
> regformfields.label != ''
> ORDER BY regfields.saveorder;
>
> In this particular query, there can be multiple occurrences of thename(can
> be filtered by DISTINCT), therefore multiple occurrences of thelabel
(which
> can't be filtered by DISTINCT, as it is always different for the same
> thename), but fieldsname is always unique.
>
> I don't care which thename or which thelabel is returned, but I only want
> one (these two tables, together with some others, construct a schema for
> yet others...), i.e thename = 'email' may be returned twice in this result
> set, but I only want it to appear once. DISTINCT, as it is used here, does
> not return what I want, as thelabel will rarely, if ever, be distinct.
>
> The ideal query would force the DISTINCT to be related ONLY to thename,
and
> return whatever thelabel it happens to grab, based on however it is
> indexing, which would be the first saveorder it stumbles upon.
>
> Any help would be appreciated!
>
> TIA,
> --Scott Brown
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query syntax help?

[Scott Brown]

OK, I am having a bit of trouble designing a MySQL query that returns what 
I want. Here is the query as I have it thus far:

SELECT DISTINCT regformfields.name AS thename,
	  regformfields.label AS thelabel,
	  regfields.name AS fieldsname
   FROM regformfields
   INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE 
regformfields.label != ''
   ORDER BY regfields.saveorder;

In this particular query, there can be multiple occurrences of thename(can 
be filtered by DISTINCT), therefore multiple occurrences of thelabel (which 
can't be filtered by DISTINCT, as it is always different for the same 
thename), but fieldsname is always unique.

I don't care which thename or which thelabel is returned, but I only want 
one (these two tables, together with some others, construct a schema for 
yet others...), i.e thename = 'email' may be returned twice in this result 
set, but I only want it to appear once. DISTINCT, as it is used here, does 
not return what I want, as thelabel will rarely, if ever, be distinct.

The ideal query would force the DISTINCT to be related ONLY to thename, and 
return whatever thelabel it happens to grab, based on however it is 
indexing, which would be the first saveorder it stumbles upon.

Any help would be appreciated!

TIA,
--Scott Brown


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query syntax help

[Daren Cotter]

What you had looks fine except the date...change what you had to:

AND date >= '2002-03-17';   # date needs quotes around it

Should work.



-Original Message-
From: rory oconnor [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 21, 2002 8:49 AM
To: mysql list (choose midget)
Subject: Query syntax help


I'm trying to figure out a query that will tell me the total number of
people in our house email file that physically opted out in the last
week.  I'm a bit of a mysql newbie as you can tell...

This is the general concept, though it doesn't seem to work:

select count(id) from contact   # my data table
where optin='no'# shows they are an opt-out
AND bad_email IS NULL   # is ticked if it was a bounceback opt-out
AND email IS NOT NULL   # show only for records that have emails
AND date >= 2002-03-17; # show data only since last sunday

I appear to be getting hung up on the date part.  I'm not sure if I can
use that kind of operator on a date with that format.  Any help is
appreciated!

Thanks,

Rory


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query syntax help

[Roger Baklund]

> I'm trying to figure out a query that will tell me the total number of
> people in our house email file that physically opted out in the last
> week.  I'm a bit of a mysql newbie as you can tell...
> 
> This is the general concept, though it doesn't seem to work:
> 
> select count(id) from contact   # my data table 
> where optin='no'  # shows they are an opt-out
> AND bad_email IS NULL # is ticked if it was a bounceback opt-out
> AND email IS NOT NULL # show only for records that have emails
> AND date >= 2002-03-17;   # show data only since last sunday
> 
> I appear to be getting hung up on the date part.  I'm not sure if I can
> use that kind of operator on a date with that format.  Any help is
> appreciated!

You need to put the date constant in quotes:

 ... AND date >= '2002-03-17';

-- 
Roger

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query syntax help

[Rick Emery]

AND date >= "2002-03-17";

-Original Message-
From: rory oconnor [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 21, 2002 10:49 AM
To: mysql "list (choose midget)
Subject: Query syntax help


I'm trying to figure out a query that will tell me the total number of
people in our house email file that physically opted out in the last
week.  I'm a bit of a mysql newbie as you can tell...

This is the general concept, though it doesn't seem to work:

select count(id) from contact   # my data table 
where optin='no'# shows they are an opt-out
AND bad_email IS NULL   # is ticked if it was a bounceback opt-out
AND email IS NOT NULL   # show only for records that have emails
AND date >= 2002-03-17; # show data only since last sunday

I appear to be getting hung up on the date part.  I'm not sure if I can
use that kind of operator on a date with that format.  Any help is
appreciated!

Thanks,

Rory


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query syntax help

[rory oconnor]

I'm trying to figure out a query that will tell me the total number of
people in our house email file that physically opted out in the last
week.  I'm a bit of a mysql newbie as you can tell...

This is the general concept, though it doesn't seem to work:

select count(id) from contact   # my data table 
where optin='no'# shows they are an opt-out
AND bad_email IS NULL   # is ticked if it was a bounceback opt-out
AND email IS NOT NULL   # show only for records that have emails
AND date >= 2002-03-17; # show data only since last sunday

I appear to be getting hung up on the date part.  I'm not sure if I can
use that kind of operator on a date with that format.  Any help is
appreciated!

Thanks,

Rory


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query syntax: multiple foreign keys

[=James Birkholz=]

Wow, our posts crossed in the mail and you suggested the same approach that
I discovered independently!
(Great minds think alike, right?)
I'm working remotely, the server is many states away, and I don't have a
local developement environment. I'm modifying the actual working site,
though I try to develop new modules in segregated files and rename the or
cut and paste the changes into the live files. I don't have the resources
to create a local development.

As I've said, I'm using phpMyAdmin with a browser working remotely.

I'll see if the query works without the renaming modification after I build
some php code to use it. I'm curious about why the front-end can introduce
this problem. The query should be the same. The results from mysql should
be the same. The display of the results, I can sort of understand, but why
just the one column? I wonder what other surprises phpMyAdmin has in store
for me. (I'll be using a lot of queries that self-reference the same table:
"Places" where a record could be for a town, that belongs to another record
for a county, that belongs to another record for a state, etc..)

Thanks for the assistance!

James

At 7:01 PM -0500 1/12/02, Roger Baklund wrote:
---snip---
>ok... you are obviously using some kind of front-end tool, not the mysql
>client. The   is a html character, in a browser it looks like a space
>character. It is used in html to prevent a linebreak at that position.
>
>Some frontend tools may have a problem with multiple fields with the same
>name. Try this:
>
>SELECT L.Name as LName, C.Name as CName ...
>
>> Here are the dumps:
>
>I took a quick look, seems ok. I think the above name issue is your problem.
>You should always try your queries in the mysql client, to eliminate
>problems related to the front-end tool or script.
>
>--
>Roger




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query syntax: multiple foreign keys

[=James Birkholz=]

I'm going re-post this query. Roger Backlund had been attempting to help me
but I've either stumped him or he's busy having a life  :)  Besides, my
first posting had several problems, since I was working from memory.
Finally, in the process of preparing this query, I found a small change
that makes it work, but I would like to know why that change is necessary.
Also, I don't want to have to use unique column names throughout the
database, just to make mySQL queries work correctly.

What I did that finally made the query work correctly was to modify the
column names in the two tables that had the same column name.

This is the syntax that works:
SELECT Languages.LName, Competencies.CName, Researchers2Languages.Researcher
FROM (Languages INNER JOIN Researchers2Languages ON Languages.Language =
Researchers2Languages.Language) INNER JOIN Competencies ON
Researchers2Languages.Competence = Competencies.Competence
WHERE Researchers2Languages.Researcher = 39;

results in :
>LNameCName   Researcher
>English   Native speaker39
>German   Can read  39

But with:
SELECT Languages.Name, Competencies.Name, Researchers2Languages.Researcher
FROM (Languages INNER JOIN Researchers2Languages ON Languages.Language =
Researchers2Languages.Language) INNER JOIN Competencies ON
Researchers2Languages.Competence = Competencies.Competence
WHERE Researchers2Languages.Researcher = 39;

(The difference is in the two column names [.LName vs .Name, .CName vs
.Name] in the first lines.)

results in:
>Name Name Researcher
>Native speaker  Native&.nbsp.speaker  39
>Can readCan&.nbsp.read 39

I have no problem doing that if I don't include the Competencies table. But
as soon as I add the third table, the results get weird, primarily putting
the Competency.Name in the column assigned to Language.Name and to the
column assigned to Competency.Name, but converting any spaces into & nbsp ;

Can anyone explain why mysql produced this weird result, even though the
columns with the same name were qualified with the table names?

TIA,
James Birkholz
Admin of Posen-L mailing list & website

(Here is some more info, if it helps:)

This is for a genealogy website.
I have 3 tables involved: a Researcher2Languages linking table that
contains 0 or more records for every record in a Researcher table (which is
not directly involved in this query). Researcher2Languages contains foreign
keys to the two other tables; Languages, which is a lookup list of 200+
modern spoken languages (autonumber primary key); and Competencies, a
lookup list of how well someone knows the language (currently three
possible values, 1-3, also autonumber primary key).

Environment: remote connection to a Linux/Apache/MySQL/php server
administered by someone else.
Using phpMyAdmin to test queries. I presume that if it works there, I can
then write some php code to use it. I've tried creating a functioning
mirror of the database and query in Access97 (which I'm fairly familiar
with) and trying to use the sql query in phpMyAdmin, but it won't run
without modifying the syntax


>
>Database PosenL running on localhost
>Showing records 0 - 2 (2 total)
>
>SQL-query : [Edit]
>SELECT L.Name, C.Name, R.Researcher FROM Researchers2Languages R LEFT JOIN
>Languages L ON L.Language = R.Language LEFT JOIN Competencies C ON
>C.Competence = R.Competence WHERE R.Researcher = 39 LIMIT 0, 30
>
>Name   Name   Researcher
>Native speaker  Native&.nbsp.speaker  39
>Can read  Can&.nbsp.read  39
>
>Note that I'm still getting C table values in the B value column, and still
>getting the " " on the B column values
>
>Here are the dumps:
>
>desc Researchers2Languages
>Field Type   Null  Key  Default  Extra
>Researcher   smallint(6)   MUL 0
>Language  smallint(6) 0
>Competence tinyint(4)  YES   NULL
>
>desc Languages
>FieldTypeNull  Key  Default  Extra
>Language smallint(6)   PRI NULL auto_increment
>Name   varchar(25)   MUL
>ShortNamechar(3)
>Rank tinyint(4) MUL 0
>Westernchar(1)
>
>desc Competencies
>Field   Type Null  Key  Default  Extra
>Competencetinyint(4)   PRI NULL auto_increment
>Name  varchar(15)
>
>Researcher  Language  Competence
>39   1   1
>39   2   3
>
> Language  Name ShortName  Rank  Western
>  1 English   ENG1   Y
>  2 German  GER2   Y
>  3 Polish POL3   Y
>  4 FrenchFRE4Y
>(remaining 200+ records left out)
>
>Competence  Name
>1   Native speaker
>2   Can translate
>3   Can read
>
>Hope you can spot something...
>Thanks!




--

RE: Query syntax: multiple foreign keys

[Roger Baklund]

> I'm going to send direct from the programming computer, using a different
> e-mail address.

Ok, I reply to the list, but CC to you. Think I found your problem...

> I did find that I had one invalid foreign key value in table A, but my
> problem remains...
>
> I tried this:
>
> Database PosenL running on localhost
> Showing records 0 - 2 (2 total)
>
> SQL-query : [Edit]
> SELECT L.Name, C.Name, R.Researcher FROM Researchers2Languages R
> LEFT JOIN
> Languages L ON L.Language = R.Language LEFT JOIN Competencies C ON
> C.Competence = R.Competence WHERE R.Researcher = 39 LIMIT 0, 30
>
> Name   Name   Researcher
> Native speaker  Native speaker  39
> Can read  Can read  39
>
> Note that I'm still getting C table values in the B value column,
> and still
> getting the " " on the B column values

ok... you are obviously using some kind of front-end tool, not the mysql
client. The   is a html character, in a browser it looks like a space
character. It is used in html to prevent a linebreak at that position.

Some frontend tools may have a problem with multiple fields with the same
name. Try this:

SELECT L.Name as LName, C.Name as CName ...

> Here are the dumps:

I took a quick look, seems ok. I think the above name issue is your problem.
You should always try your queries in the mysql client, to eliminate
problems related to the front-end tool or script.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query syntax: multiple foreign keys

[Roger Baklund]

* =James Birkholz=
> In a message dated 1/12/02 10:48:45 AM Central Standard Time,
> [EMAIL PROTECTED] writes:
> ---snip---
> << SELECT A.Name, B.Name, P.ID
>FROM Persons P
>LEFT JOIN QualityA A USING(A_ID)
>LEFT JOIN QualityB B USING(B_ID)
>WHERE P.ID = thatGuy; >>
> ---snip---
>
> That doesn't work, get an error as it tries to find B_ID in the A table.

Sorry, my bad. USING() depends on the table immediately preceeding it.  Try
this:

SELECT A.Name, B.Name, P.ID
  FROM Persons P
  LEFT JOIN QualityA A ON A.A_ID=P.A_ID
  LEFT JOIN QualityB B ON B.B_ID=P.B_ID
  WHERE P.ID = 1;

> I tried swapping the partners on the first LEFT JOIN...
>
> SELECT A.Name, B.Name, P.ID
>FROMQualityA A
>LEFT JOIN Persons P USING(A_ID)
>LEFT JOIN QualityB B USING(B_ID)
>WHERE P.ID = thatGuy
>
> but that gives me more strange results
> (using my example IDs:)
>
> Name  NameResearcher
> "A Quality"   "AQuality"  thatGuy
> "B Quality"   "B Quality"thatGuy

If the above does not help, post the output of "desc Persons", "desc
QualityA", "desc QualityB" and "select * ..." from the same three tables.

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query syntax: multiple foreign keys

[=James Birkholz=]

In a message dated 1/12/02 10:48:45 AM Central Standard Time,
[EMAIL PROTECTED] writes:
---snip---
<< SELECT A.Name, B.Name, P.ID
   FROM Persons P
   LEFT JOIN QualityA A USING(A_ID)
   LEFT JOIN QualityB B USING(B_ID)
   WHERE P.ID = thatGuy; >>
---snip---

That doesn't work, get an error as it tries to find B_ID in the A table.
I tried swapping the partners on the first LEFT JOIN...

SELECT A.Name, B.Name, P.ID
   FROMQualityA A
   LEFT JOIN Persons P USING(A_ID)
   LEFT JOIN QualityB B USING(B_ID)
   WHERE P.ID = thatGuy

but that gives me more strange results
(using my example IDs:)

NameNameResearcher
"A Quality" "AQuality"  thatGuy
"B Quality" "B Quality"thatGuy


Here is the actual query and results, using the IDs that I am really using:

SELECT L.Name, C.Name, R.Researcher FROM Languages L LEFT JOIN
Researchers2Languages R USING(Language) LEFT JOIN Competencies C
USING(Competence) WHERE R.Researcher = 39 LIMIT 0, 30

NameNameResearcher
English English 39
Can readCan read   39

The correct results would be

L.Name  C.Name  R.Researcher
English Native speaker  39
German  Can read39

Trying to think of anything that might be use, I'll mention that I'm using
SMALLINT for the foreign key "Language" and TINYINT for the "Competency" FK.
In this situation, I *could* hard code C data into the A table, but I use
similar constructs all the time and need to get a handle on this. If I need
to describe the conditions better, I will be happy to.

Thanks in advance for any suggestions.

James Birkholz
Admin of Posen-L mailing list & website




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query syntax: multiple foreign keys

[DL Neil]

James,

> I'm new to the list, to mysql and to dynamic website programming. I'm not
> new to programming, had my nose in Access97 for the last few years, off and
> on. So I'm used to being coddled with sql and can't find a syntax that
> works for this situation:
> 
> (I'm using phpMyAdmin to work with the database and test the queries. The
> server is remote.)
> 
> 3 tables: Persons, QualityA, QualityB
> 
> Table Persons contains integer foreign keys for the other two tables.
> 
> I need a query that "for a certain Person in table Persons, returns
> QualityA.Name and QualityB.Name.
> 
> If I use:
> SELECT A.Name, B.Name, P.ID
> FROM Persons P, QualityA A, QualityB B
> WHERE P.ID = thatGuy, P.A_ID = A.A_ID, P.B_ID = B.B_ID;
> 
> I get strange results
> 
> Name Name ID
> "B Quality" "B Quality" thatGuy
> 
> instead of
> 
> Name Name ID
> "A Quality" "B Quality" thatGuy
> 
> To be specific, for the A.Name column of the results, I get the correct
> value for the B.Name; for the B.Name column, I get the same value, but it
> replaces the space char in the value with " " ?!
> 
> I've tried many different combinations, but either get similar weird
> results or error messages.
> If I throw out the QualityB table, I can get it to work...
> 
> SELECT A.Name, P.ID
> FROM Persons P, QualityA A
> WHERE P.ID = thatGuy, P.A_ID = A.A_ID;
> 
> Name ID
> "A Quality" thatGuy
> 
> Note: I use e-mail on a different machine/platform than I use for the
> dynamic website programming, so I'm writing from memory, using different
> identifiers for this posting than the actual ones used in my database.
> 
> I have limited time to work on this project and will greatly appreciate a
> hand getting over this wall. I've searched for hours on the net, and tried
> the IRC channels, but everything is written for one or two table queries.


Change the WHERE clause so that the three comparisons are joined in one logical 
expression/result:

SELECT A.Name, B.Name, P.ID
 FROM Persons P, QualityA A, QualityB B
 WHERE P.ID = thatGuy AND 
  P.A_ID = A.A_ID AND
  P.B_ID = B.B_ID;

=dn


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Query syntax: multiple foreign keys

[=James Birkholz=]

I'm new to the list, to mysql and to dynamic website programming. I'm not
new to programming, had my nose in Access97 for the last few years, off and
on. So I'm used to being coddled with sql and can't find a syntax that
works for this situation:

(I'm using phpMyAdmin to work with the database and test the queries. The
server is remote.)

3 tables: Persons, QualityA, QualityB

Table Persons contains integer foreign keys for the other two tables.

I need a query that "for a certain Person in table Persons, returns
QualityA.Name and QualityB.Name.

If I use:
SELECT A.Name, B.Name, P.ID
FROM Persons P, QualityA A, QualityB B
WHERE P.ID = thatGuy, P.A_ID = A.A_ID, P.B_ID = B.B_ID;

I get strange results

NameNameID
"B Quality" "B Quality"thatGuy

instead of

NameNameID
"A Quality" "B Quality" thatGuy

To be specific, for the A.Name column of the results, I get the correct
value for the B.Name; for the B.Name column, I get the same value, but it
replaces the space char in the value with " " ?!

I've tried many different combinations, but either get similar weird
results or error messages.
If I throw out the QualityB table, I can get it to work...

SELECT A.Name, P.ID
FROM Persons P, QualityA A
WHERE P.ID = thatGuy, P.A_ID = A.A_ID;

NameID
"A Quality" thatGuy

Note: I use e-mail on a different machine/platform than I use for the
dynamic website programming, so I'm writing from memory, using different
identifiers for this posting than the actual ones used in my database.

I have limited time to work on this project and will greatly appreciate a
hand getting over this wall. I've searched for hours on the net, and tried
the IRC channels, but everything is written for one or two table queries.

James Birkholz
Admin of Posen-L mailing list & website



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query syntax question

[Benjamin Pflugmann]

Hi.

On Wed, Nov 28, 2001 at 02:26:45PM +0900, [EMAIL PROTECTED] wrote:
> i want to do this:
> 
> SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId =
> language.languageId AND language.isoLanguageId = 25) AND
> (matrix.toLanguageId = language.languageId AND language.isoLanguageId = 27);
> 
> but the grouping doesn't seem to be working. it this a MySQL limitation or
> (more likely) the manifestation of my limited SQL knowledge? what have i got
> wrong?
> also, if there's a better way to do what i'm trying to do here, please let
> me know.

Well, I am not sure what you want to archieve, but grouping "AND"
makes no difference, because (a AND b) AND c = a AND (b AND c).

> these work, but aren't what i want:
> 
> SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId =
> language.languageId AND language.isoLanguageId = 25) AND
> (matrix.toLanguageId = 27);
> 
> SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId =
> 25) AND (matrix.toLanguageId = language.languageId AND
> language.isoLanguageId = 27);

Sorry, I cannot come up with a solution, because I am still not sure,
what you are trying to do. Could you post a little example which will
illustrate what you are getting with the above query and what you are
expecting.

Bye,

Benjamin.

-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

query syntax question

[Patrick Bolduan]

i want to do this:

SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId =
language.languageId AND language.isoLanguageId = 25) AND
(matrix.toLanguageId = language.languageId AND language.isoLanguageId = 27);

but the grouping doesn't seem to be working. it this a MySQL limitation or
(more likely) the manifestation of my limited SQL knowledge? what have i got
wrong?
also, if there's a better way to do what i'm trying to do here, please let
me know.

these work, but aren't what i want:

SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId =
language.languageId AND language.isoLanguageId = 25) AND
(matrix.toLanguageId = 27);

SELECT matrix.matrixId FROM matrix, language WHERE (matrix.fromLanguageId =
25) AND (matrix.toLanguageId = language.languageId AND
language.isoLanguageId = 27);


thanks,

patrick bolduan


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Servlet SELECT Query syntax error

[David Lidström]

 hi

  try to use "'" around the new value.
  I.E.

   rs = stmt.executeQuery("SELECT * FROM MyTable WHERE title_name='"
+ request.getParameter("title_name") ) + "'";

/david


-Original Message-
From: Lucy [mailto:[EMAIL PROTECTED]]
Sent: den 30 maj 2001 15:09
To: [EMAIL PROTECTED]
Subject: Servlet SELECT Query syntax error


Hi
Error message "You have error in your SQL syntax" ... Using Apache Tomcat -
MySQL in Win 2000.
Scenario:  Servlet collects form data value, sends query to MySQL, returns
search.  The problem is getting
the value of the inputted parameter named, title_name.  Problem coding:

rs = stmt.executeQuery("SELECT * FROM MyTable WHERE title_name="  +
request.getParameter("title_name") );

I've tried various combos after the problem part** WHERE title_name=" **
such as double quotes, single, both, ?, ;,spaces, also replacing with String
alternatives eg.,
   String param1;
   param1 = request.getParameter("title_name");
   rs = stmt.executeQuery("SELECT * FROM MyTable WHERE title_name = " +
param1);

I've checked parameter name matches html form, servlet and database and that
rs closes OK.  Any ideas?  Cheers, Lucy


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Servlet SELECT Query syntax error

[Lucy]

Hi
Error message "You have error in your SQL syntax" ... Using Apache Tomcat -
MySQL in Win 2000.
Scenario:  Servlet collects form data value, sends query to MySQL, returns
search.  The problem is getting
the value of the inputted parameter named, title_name.  Problem coding:

rs = stmt.executeQuery("SELECT * FROM MyTable WHERE title_name="  +
request.getParameter("title_name") );

I've tried various combos after the problem part** WHERE title_name=" **
such as double quotes, single, both, ?, ;,spaces, also replacing with String
alternatives eg.,
   String param1;
   param1 = request.getParameter("title_name");
   rs = stmt.executeQuery("SELECT * FROM MyTable WHERE title_name = " +
param1);

I've checked parameter name matches html form, servlet and database and that
rs closes OK.  Any ideas?  Cheers, Lucy


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re[2]: select query syntax

[Igor V Yermakov]

no , i havn't any key with AUTO_INCREMENT option
i wont numbered my rows on fly! how to do it.
pleease help me!!!


RT> supposed you numbered all rows in the table incrementally in field nid:
RT> select mydata from mytable where mod(nid, N)=offset;
RT> fill in N and offset (0..n-1)
RT> On Sat, 28 Apr 2001 14:26:46 +0400
RT> Igor V Yermakov <[EMAIL PROTECTED]> wrote:

>> i have 400 rows in my mysql database table
>> and i wont get each N row from table (say each sixth row)???
>> please help me???



-- 
Best regards,
 Igormailto:[EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: select query syntax

[Rene Tegel]

supposed you numbered all rows in the table incrementally in field nid:

select mydata from mytable where mod(nid, N)=offset;

fill in N and offset (0..n-1)


On Sat, 28 Apr 2001 14:26:46 +0400
Igor V Yermakov <[EMAIL PROTECTED]> wrote:

> i have 400 rows in my mysql database table
> and i wont get each N row from table (say each sixth row)???
> please help me???
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

select query syntax

[Igor V Yermakov]

i have 400 rows in my mysql database table
and i wont get each N row from table (say each sixth row)???
please help me???



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Help with Query Syntax?

[Roger Ramirez]

Hmmm... Looks like you over did the query to me.  This should work.

SELECT o.OrderID, c.CustomerFirst, c.CustomerLast, s.SalesRepFirst,
s.SalesRepLast
FROM Orders as o, Customers as C, SalesReps as s
WHERE O.CustomerPhone=c.CustomerPhone AND o.SalesRepID=s.SalesRepID

of course you don't need the o.OrderID but I usually like selecting the ID
from the main table being queried.

- Original Message -
From: "Lee Jenkins" <[EMAIL PROTECTED]>
To: "MySQL Mail list" <[EMAIL PROTECTED]>
Sent: Monday, January 29, 2001 10:18 PM
Subject: Help with Query Syntax?


>
>
> Hi all.  I'm having a little trouble with this query.
>
> **Table 1 (Orders) **
>
> OrderID SmallInt
> OrderStatus   VarChar(20)
> CustomerPhone   VarChar(10)
> SalesRepID   SmallInt(11)
>
> **Table 2 (Customers)**
>
> CustomerFirst   VarChar(20)
> CustomerLastVarChar(20)
> CustomerPhone   VarChar(10)
>
> **Table 3 (SalesReps)**
> SalesRepID  SmallInt(11)
> SalesRepFirst   VarChar(20)
> SalesRepLast   VarChar(20)
>
>
> I'm trying to retrieve all ORDERS with the CustomerFirst and CustomerLast
> and the SalesRepFirst and SalesRepLast from these respective tables.
There
> are other fields and they are listed in the SQL statement below.  The ODBC
> driver informs me that there is a problem with the JOINS.  I'm very new to
> MySQL syntax.  Can  you join more than one table?
>
>
> SELECT DISTINCT deliverycustomers.CustomerFirst,
> deliverycustomers.CustomerLast,
> deliverycustomers.CustomerAddress1,
> deliverycustomers.HouseNumber, orders.OrderID,
> servers.ServerFirst,
> servers.ServerLast, deliverycustomers.CustomerPhone,
> orders.OrderTime
> FROM
> servers INNER JOIN deliverycustomers INNER JOIN orders ON
> deliverycustomers.CustomerPhone = orders.CustomerPhone ON
> servers.ServerID =
> orders.DriverID GROUP BY deliverycustomers.CustomerFirst,
> deliverycustomers.CustomerLast,
> deliverycustomers.CustomerAddress1,
> deliverycustomers.HouseNumber, orders.OrderID,
> servers.ServerFirst,
> servers.ServerLast, deliverycustomers.CustomerPhone,
> orders.OrderTime
> WHERE
> Orders.OrderType = 'Delivery' AND (Orders.OrderStatus = 'Open'
> OR  Orders.OrderStatus = 'PreAuth') ORDER BY OrderTime;
>
>
>
> Any help would be greatly appreciated.
> Lee Jenkins
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Help with Query Syntax?

[Lee Jenkins]

Hi all.  I'm having a little trouble with this query.

**Table 1 (Orders) **

OrderID SmallInt
OrderStatus   VarChar(20)
CustomerPhone VarChar(10)
SalesRepIDSmallInt(11)

**Table 2 (Customers)**

CustomerFirst VarChar(20)
CustomerLastVarChar(20)
CustomerPhone   VarChar(10)

**Table 3 (SalesReps)**
SalesRepID  SmallInt(11)
SalesRepFirst VarChar(20)
SalesRepLast  VarChar(20)


I'm trying to retrieve all ORDERS with the CustomerFirst and CustomerLast
and the SalesRepFirst and SalesRepLast from these respective tables.  There
are other fields and they are listed in the SQL statement below.  The ODBC
driver informs me that there is a problem with the JOINS.  I'm very new to
MySQL syntax.  Can  you join more than one table?


SELECT DISTINCT deliverycustomers.CustomerFirst,
deliverycustomers.CustomerLast,
deliverycustomers.CustomerAddress1,
deliverycustomers.HouseNumber, orders.OrderID,
servers.ServerFirst,
servers.ServerLast, deliverycustomers.CustomerPhone,
orders.OrderTime
FROM
servers INNER JOIN deliverycustomers INNER JOIN orders ON
deliverycustomers.CustomerPhone = orders.CustomerPhone ON
servers.ServerID =
orders.DriverID GROUP BY deliverycustomers.CustomerFirst,
deliverycustomers.CustomerLast,
deliverycustomers.CustomerAddress1,
deliverycustomers.HouseNumber, orders.OrderID,
servers.ServerFirst,
servers.ServerLast, deliverycustomers.CustomerPhone,
orders.OrderTime
WHERE
Orders.OrderType = 'Delivery' AND (Orders.OrderStatus = 'Open'
OR  Orders.OrderStatus = 'PreAuth') ORDER BY OrderTime;



Any help would be greatly appreciated.
Lee Jenkins


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php