|
|
Posted by AdrianG on 04/04/07 01:36
On Apr 3, 5:00 pm, "Brad" <Brad.Marsh...@Teksouth.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Thanks Brad - normalizing the data will surely help but still I can't
see a quick way of retrieving the concatenated "sports" fields. The
real data is a bit more complicated than the sample that I mentioned
but the idea is the same.
Navigation:
[Reply to this message]
|