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]