# How define select, if bridge pair may have 3 players?

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players.

Table, members:

pair, player_id

1 1
1 2
2 3
2 4
3 5
3 6
4 7
4 8
5 9
5 10
6 11
6 12
6 13

table players:

id name

1 Smith
2 Brown
3 Johnson
4 Wild
5 Hammer
6 Bolt
7 Pink
8 Bush
9 Novak
10 King
11 Holmes
12 Watson
13 Gold

Required result of select is:

Pair, Names
1, Smith - Brown
2, Johnson - Wild
3, Hammer - Bolt
4, Pink - Bush
5, Novak - King
6, Holmes - Watson - Gold
-

This is the MySQL solution (very similar to Leigh's Oracle solution):

SELECT Pair
, GROUP_CONCAT(name SEPARATOR ' - ') Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;

Obligatory sqlfiddle which was modified from Leigh's illustration as well.*

* I give credit when I plagiarize!

-
Credit to Lamak as my SQL Fiddle was modified from his. – Leigh Riffel Nov 14 '12 at 16:51

This works in SQL Server 2005+ The "magic" is using XML to concatenate a group of strings per pair. Hope this helps!

WITH Pairs
(
Pair
)
AS
(
SELECT DISTINCT
m.pair AS Pair
FROM dbo.members AS m
)
SELECT
p.Pair,
REPLACE
(
REPLACE
(
REPLACE
(
CONVERT(VARCHAR(MAX), X.n),
'</PlayerName><PlayerName>',
' - '
),
'</PlayerName>',
''
),
'<PlayerName>',
''
) AS ConcatNames
FROM Pairs AS p

CROSS APPLY --Create XML string
(
SELECT
pl.name AS PlayerName
FROM dbo.players AS pl

INNER JOIN dbo.members AS m
ON pl.id = m.player_id

WHERE m.pair = p.Pair

ORDER BY
pl.id ASC

FOR XML PATH(''), TYPE
) AS X(n)

ORDER BY
p.Pair ASC;
-
Looks like we came up with about the same response at about the same time. Although I think the shorter version is easier to read, yours is more efficient (not needing a worktable), so +1 for you. – Kevin Feasel Nov 14 '12 at 13:30
+1 This is also working. An, another sqlfiddle with a demo: sqlfiddle.com/#!3/f68b9/7 – Lamak Nov 14 '12 at 13:33

The most preferable way to do this is to do whatever formatting you need to in something above the SQL layer. This is pretty easy to do in a lot of reporting tools or in languages which manipulate object arrays efficiently.

If you're using T-SQL, you can use FOR XML PATH to do the job.

select distinct
mo.pair,
names = stuff(
(
select ' - ' + p.Name
from players p
inner join members m on m.player_id = p.id
where mo.pair = m.pair
order by m.player_id
for xml path ( '' )
), 1, 3, ''
)
from
members mo;
-
+1, This works great. Here is an sqlfiddle with this example: sqlfiddle.com/#!3/f68b9/6 – Lamak Nov 14 '12 at 13:30
+1 for mentioning that the preferable way to perform this is outside of T-SQL. For kicks, I compared the execution plans of our two solutions, by running them in the same batch and forcing a plan recompile. I show that your query costs 73% relative to the batch, and mine costs 27%. For this small sample, this means next to nothing. However, I would be interested to see how both of our queries scale to a large data set. Good stuff! – Matt M Nov 14 '12 at 13:38
Thank Kevin for your reply. How to implement in MYSQL? – otm Nov 14 '12 at 14:31
I'm not very familiar with MySQL, but you can try the bottom of this post: postgresonline.com/journal/archives/…. It shows how to do something similar to this for Postgres, SQL Server, and MySQL. It looks like it's actually really easy to do in MySQL. – Kevin Feasel Nov 14 '12 at 14:40

Oracle 11.2+ solution (SQL Fiddle):

SELECT Pair, LISTAGG(Name,', ') WITHIN GROUP (ORDER BY id) Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;
-
Here is the PostgreSQL version of your SQLFiddle: sqlfiddle.com/#!12/4ae8b/6 – a_horse_with_no_name Nov 15 '12 at 12:37

Since the question is not tagged a specific DBMS, here's a "standards-compliant" solution - it stays away from FOR-XML, GROUP_CONCAT and LISTAGG tricks. It works for only up to 3 members in a "pair" (set). The code works as-is in PostgreSQL.

SELECT m1.Pair,
p1.name || ' - ' || p2.name ||
COALESCE(' - ' || p3.name, '') AS Names
FROM Members m1
JOIN Members m2 on m1.pair = m2.pair and m1.player_id < m2.player_id
LEFT JOIN Members m3 on m2.pair = m3.pair and m2.player_id < m3.player_id
JOIN Players p1 ON m1.player_id = p1.id
JOIN Players p2 ON m2.player_id = p2.id
LEFT JOIN Players p3 ON m3.player_id = p3.id
WHERE p3.id is not null or NOT EXISTS (
SELECT *
FROM Members m4
WHERE m4.pair=m2.pair
AND m4.player_id NOT IN (m1.player_id,m2.player_id))
ORDER BY m1.Pair;

The problem with standards is that, well, there are many. See here for conformance the string concatenation operator (||). For this solution to be applied to MySQL, the top bit has to be written using CONCAT()

SELECT m1.Pair,
CONCAT(p1.name, ' - ', p2.name,
COALESCE(Concat(' - ', p3.name), '')) AS Names

SQL Server uses the + operator.
Note: Even though SQL Server 2012 supports the CONCAT() function, it implements the same Oracle bug, i.e. CONCAT(' - ', NULL) => ' - ' instead of NULL.

SELECT m1.Pair,
p1.name + ' - ' + p2.name +
COALESCE(' - ' + p3.name, '') AS Names

Oracle will require a CASE statement to get around the concatenation-with-NULL bug mentioned above.

SELECT m1.Pair,
p1.name || ' - ' || p2.name ||
CASE when p3.name is null then '' else ' - ' || p3.name END AS Names

## Performance

Despite the NOT EXISTS anti-semijoin, when put against Kevin and Matt's SQL Server solutions, it produces an estimated query cost of

Matt:Kevin:Richard = 24%: 65% : 11%

## PostgreSQL 9.1+, SQL Server 2012+, Oracle, MySQL, DB2

The following query (SQLFiddle) proposed by Leigh in the comments works on all the listed DBMS:

SELECT m1.Pair
, CONCAT(CONCAT(CONCAT(p1.name, ' - '), p2.name),
COALESCE((SELECT CONCAT(' - ', p3.name) FROM Players p4 WHERE p4.id = p3.id),'')) AS Names
FROM Members m1
JOIN Members m2 on m1.pair = m2.pair and m1.player_id < m2.player_id
LEFT JOIN Members m3 on m2.pair = m3.pair and m2.player_id < m3.player_id
JOIN Players p1 ON m1.player_id = p1.id
JOIN Players p2 ON m2.player_id = p2.id
LEFT JOIN Players p3 ON m3.player_id = p3.id
WHERE p3.id is not null or NOT EXISTS (
SELECT *
FROM Members m4
WHERE m4.pair=m2.pair
AND m4.player_id NOT IN (m1.player_id,m2.player_id))
ORDER BY m1.Pair

The real trick is in getting the string concatenation correct, SQL Server is the last on the list to add a CONCAT() function. However, a function by the same name across 5 DBMS is by no means a standard, since it behaves differently. Already mentioned above is that Oracle treats NULLs in CONCAT as empty strings (''), and PostgreSQL requires that operands are strings (will not auto-cast).

-
+1 for a very complete answer an for showing another option. That said, I think that this solution is rather limited to the example posted and is difficult to create a solution with an unknown number of elements to concatenate – Lamak Nov 15 '12 at 2:21
@Lamak So you're saying it's not an answer to a question titled "may have 3 players"? – 孔夫子 Nov 15 '12 at 2:40
Nope, I do think that is a very thorough answer, thats why I upvoted it; if I thougt otherwise I would'nt have – Lamak Nov 15 '12 at 12:13
Very nice. concat() is actaully a standard function and one of the few things that does exist in nearly all DBMS. So it could be used to write "portable" SQL to a certain extent. And the "not null" problem can be handled by using the standard coalesce() function (which is a bit more compact than a CASE) – a_horse_with_no_name Nov 15 '12 at 12:34
@a_h Coalesce? I can't see how that handles the COALESCE(' - ' || p3.name, '') part for Oracle. When p3.name is null, it leaves the unwanted ' - ' – 孔夫子 Nov 15 '12 at 12:38