Scott Haneda wrote:
Wonder if there is a more efficient way of dealing with the following...
I have a user table and a serial_numbers table
User table has name, address, state, zip etc Serial_numbers table has a id and a serial number.
Client wants a "hitlist" that shows the user data and the associated serial numbers for a list of users. If the query returns 20 serial numbers, I am going to get back 19 name, address, state, zip etc rows that I don't need, I can easily discard them, but this seems less than efficient.
Do you mean that you get something like
Joe Smith 123 Main St. 01234 sn1 Joe Smith 123 Main St. 01234 sn2 Joe Smith 123 Main St. 01234 sn3 Joe Smith 123 Main St. 01234 sn4 Joe Smith 123 Main St. 01234 sn5
but you'd like something like
Joe Smith 123 Main St. 01234 sn1, sn2, sn3, sn4, sn5
instead?
If you are using mysql 4.1, I believe you could use the GROUP_CONCAT function <http://www.mysql.com/doc/en/GROUP-BY-Functions.html>.
Something like:
SELECT U.name, U.address, U.zip GROUP_CONCAT(S.serial_number ORDER BY S.serial_number SEPARATOR ', ') FROM User U, Serial_numbers S WHERE S.user_id = U.user_id GROUP BY name;
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
