CODEWITHSUNDEEP
sqlselectnested
eirikrl
eirikrl

Reputation: 438

nesting sql select in another sql select?

This is a really common use case but I've never known the best way of doing it. Say I have two tables

team table
- id
- team name

players table
- id
- team_id
- player_name

Say in the output html I want to show headings of the teams, and underneath each team the respective players:

Team 1
- bob
- jon

Team 2
- ken
- jason

Previously I've always used a select statement for the teams, and while looping thru the results, do another select statment where the foreign key would equal the ID of the current row of the outer query. Is this a dumb, non-efficient way of doing it? What's the standard way of doing something like this? I could use join but I'm guess I would need to add some logic to only show the team heading once. I'm using php+mysql.

Upvotes: 1

Views: 174

Answers (3)

Ian P
Ian P

Reputation: 1724

What you are doing is conconating the player_name column. As of SQL 2008R2 there is no aggregate conconcate string function - unless you write on using the dot net framework and enable it in all databases where you use this technique.

a way which will work with all currently supported versions - 2005 +

create a user defined function which takes the id of the team and returns a string of the names (in whatever order you want)

You query then becomes

SELECT team_name, dbo.af_PayerNames(team_ID) Players FROM [Team tabel]

To conconate the string inside the function

DECLARE @Output VARCHAR(4000)
SET @Output = ''
SELECT @Output = @output + ' - ' + COALESCE(Player_Name, '') FROM Players where TeamId = @ param

You can use some fancy case logic to ensure that the separator ' - ' is only added when the @Output <> ''

You can expand your function to sort by different orders by passing a param. The solution is very readable - there probably is a sub query way of doing it - but subqueries alwways make my head hurt! The function approach is not a way to go with very large tables - your brain probaly has to hurt.

Hope this helps

Upvotes: 0

BuZz
BuZz

Reputation: 17495

you have two options, a join or a subquery. Each join can always be translated into a subquery in another.

The standard way to do less queries :

a first select on the whole teams table.

Then loop through your results, for x in team names : and a simple select where team_id = x for each team.

you'll notice there's no need for sub queries, just coding logic.

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6126

Generally looping through one result set and querying for more info inside the loop isn't a good idea. It's a best to avoid database queries in loops if possible. As you suggest a JOIN would work here:

SELECT team_name,player_name FROM team INNER JOIN players on team.id=players.team_id

There are a number of ways you could process the results in PHP. You could loop through the results and add them to an array

array(
    team1 => array(
        bob,
        jon
    ),
    team2 => array( etc

You can then loop through the array and output the results how you want. Or you could just loop directly through the db results and output them as you go storing the team name and only outputting it when it changes. I'd recommend the former solution. It's cleaner and abstracted better.

Upvotes: 2

Related Questions