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]
