SQL Help

[toptalent]

I need to query 3 tables with a many to many relationship:

Players Table Ta:

PlayerID PlayerName
1 p1
2 p2

Format Table Tb:

FormatID FormatName
1 mp3
2 wmv
3 wav

WhatPlayerPlaysWhatFormat Table Tc:

WPWFPlayerID WPWFFormatID (both foreign keys)
1 1
1 2
2 1
2 2
3 3

Now I want a query that shows all the formats that a player can play. I'm thinking about something like this:

SELECT Ta.PlayerID,Ta.PlayerName,Tb.FormatName
FROM Ta,Tb,Tc
WHERE Ta.PlayerID=Tc.WPWFPLayerID AND Tc.WPWFFormatID=Tb.FormatID
GROUP BY Ta.PlayerID

But since the Tb.FormatName will have multiple matches, I need some function to concatenate all the formats in one row. I don't know if there's such a function, or am I using the wrong approach?

Please help. Thanks.

 

[Aptitude]

That all depends. Which database you are using? There may be some kind of "Cross Tab" query you can use, though these tend to be DB specific and not standards based. I would also recommend posting the question under the forum for your database.

Generally I would say that this approach is a bad one anyway, because it does not cater for increasing or decreasing the number of formats very well.

To make life easier when returning the results from your query, you should order it by PlayerID then you can loop through the records looking for a change in ID to know when to start the new row in your display something like this:

SELECT Ta.PlayerID,Ta.PlayerName,Tb.FormatName
FROM
	Ta 
	INNER JOIN Tc ON Ta.PlayerID = Tc.WPWFPLayerID
	INNER JOIN Tb ON Tc.WPWFFormatID = Tb.FormatID
ORDER BY Ta.PlayerID

int currentPlayerId = -1;
            while (reader.Read())
            {
                if (currentPlayerId != (int)reader["PlayerId"])
                {
                    if (currentPlayerId != -1)
                    {
                        // Start new row.
                        Console.WriteLine();
                    }
                    currentPlayerId = (int)reader["PlayerId"];

                    // Write the player name
                    Console.Write("Player {0} playes", reader["PlayerName"]);                    
                }

                // Write out column
                Console.Write(" {0}", reader["FormatName"]);
            }

 

[stevexff]

You're using the wrong approach. Your table design is fine apart from the names being a bit verbose, but your query is wrong. GROUP BY is only any use when you are using summary functions like COUNT(*), AVG(*), or MAX(*).

Use a simpler query without the GROUP BY, perhaps with an additional WHERE clause to restrict the results to a single user. SQL will return a number of rows, depending on how many formats they are linked to. If you need them all on one row (for display purposes, for example), then just loop through them, catenating them to a String or stuffing them into a Collection or Array of some sort.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]