Posted by Brad on 04/03/07 21:00

On Apr 3, 2:28 pm, "AdrianG" <adrian.grigo...@altairtech.ca> wrote:
> I am trying to write a single SQL query that would retrieve the data
> that I need. For example, I have a table called Athletes that has 2
> fields: name and sport containing the name of an athlete and the
> sports that he or she participates in. Some sample data:
> John,hockey
> Michael,football
> John,swimming
> Eric,swimming
> Michael,baseball
>
> I need a SQL query that would return each athlete and the sports they
> participate in:
>
> John - hockey,swimming
> Michael - football,baseball
> Eric - swimming
>
> I've tried all sorts of joins and aggregate functions but with no
> success. I want to avoid to run a query listing the athletes and then
> doing a query for each of them to get the sports. Can anyone provide
> some tips on doing this with just one SQL query?

You need to break that out into more tables. Put the names in one
table, the sports in another table and a cross table in between. This
will be a many to many relationship and make your query a piece of
cake.

[Back to original message]