Re: Merging the results of multiple tables

[Mike Clemens]

Usually I do this sort of thing outside of the database: you could probably do it using a temporary table to hold the names, and then run a series of updates to populate it with song titles. That's a lot of thrashing around in your tables, though.

The quickest sort of merge I can think of is to display the count of songs available, and then allow the user to navigate through to a list of songs for that user:

SELECT user, CONCAT(COUNT(songs)," songs uploaded")
FROM Songs
GROUP BY user
ORDER BY user

So you would get a result like:

Bob 3 songs uploaded
Joe 2 songs uploaded
Sam 10 songs uploaded
Jane 3 songs uploaded

"Flattening" a relational structure like this is best done outside of SQL in my experience, but that's mainly because I haven't found a clean way to do it. ;-)