sql statement to retrieve a list of results (from other table) for a given record?

Question

I have the following schema:

TABLE bands
-> band_id
-> property1
-> property2
-> ...

TABLE tracks
-> band_id
-> track_id

The tracks table typically maps a single band_id onto multiple track_ids (there are multiple tracks for each band).

My goal is to build, for the bands that match certain conditions (in terms of property1, property2, etc), a data structure of the format (band_id, property1, property2, ..., [list of track_ids for that band]).

Currently I am doing

SELECT band_id, property1, property2, ..., track_id 
  FROM bands, tracks 
 WHERE bands.property1 = xyz;

This generates tuples (band_id, property1, property2, ..., track_id) for the matching bands; one tuple for each track. Currently I take this and aggregate (in my non-sql code) all track_ids for a given band into a list. But it seems very wasteful, since all but the 'propertyN' values are getting returned multiple times for each single band.

The alternative I thought of was to first get the band_ids and properties for the matching bands, and then issue a separate query for the track_ids for each band. But this, in turn, means issuing a separate query for each band -- and there are tens of thousands of them, so this probably is not very smart.

Is there a better way to do this?
I have been looking at GROUP BY and that seems to be the general direction I am interested in, but I couldn't find an aggregate function that would basically return a list of values (in this case, track_ids) for each matching record in table bands.

Answer 1

If you want to produce a string and you're using MySQL there's a GROUP_CONCAT aggregation function but there's no simple equivalent in SQL Server, at least. For SQL Server you can use FOR XML queries to produce XML output with the sort of grouping that you want but I'm not sure how helpful that is in general. (In many of my applications I use FOR XML queries to extract data from a database and then XSLT to transform it into HTML for display on the web, but I'm aware that this is a fairly specific solution rather than a general one.)

Answer 2

You can return data in the form (band_id, [list of track_ids for that band as comma separated string]) but it's not the SQL way. Look at GROUP_CONCAT (for MySQL).

The way you are currently doing it is usually better, although you should use an explicit JOIN keyword.

SELECT bands.band_id, track_id
FROM bands
LEFT JOIN tracks ON tracks.band_id = bands.band_id
WHERE bands.property1 = xyz;

You can put the results into the structure of your choice once you have read them from the database.