I'm working with a table that has a structure like this:
ID
UserID
Team1
Team2
Team3
Team4
It's a screwed-up structure, I know, restructuring as two tables in a one-to-many relationship would be better, but it's what I have to work with.
I need to output a list of teams with their associated users. The problem is I have data like this:
User: Joe Team1: Aces
User: Jill Team1: Betas
User: Kim Team2: Aces
User: Skip Team3: Deltas
User: Zed Team1: Betas
User: Joe Team2: Deltas
What I want to output would be something like this:
Aces: Joe
Aces: Kim
Betas: Jill
Betas: Zed
Deltas: Skip
Deltas: Joe
My current plan is to convert the query results to arrays (I'm using ColdFusion 8 on the server), join them, and then sort for teams. However, I wondered if there is a simpler, perhaps SQL oriented approach I'm missing. Anyone spot a better way?
A UNION of multiple queries, given the structure of your table and the data you have to work with, may be a good solution for you:
( SELECT Team1 as Team, UserName
FROM teamData as td1
WHERE td1.Team1 IS NOT NULL
AND td1.Team1 != '' )
UNION
( SELECT Team2 as Team, UserName
FROM teamData as td2
WHERE td2.Team2 IS NOT NULL
AND td2.Team2 != '' )
UNION
( SELECT Team3 as Team, UserName
FROM teamData as td3
WHERE td3.Team3 IS NOT NULL
AND td3.Team3 != '' )
UNION
( SELECT Team4 as Team, UserName
FROM teamData as td4
WHERE td4.Team4 IS NOT NULL
AND td4.Team4 != '' )
ORDER BY Team, UserName;
Which gives you:
Team UserName
Aces Joe
Aces Kim
Betas Jill
Betas Zed
Deltas Skip
Working SQLFiddle is here for testing (this was written against a MySQL db):