[web] formatting two tables into one table

Recommended Posts

Alpha_ProgDes    6921
i have a table that has columns Player, HeroType, Level, NPC, EnemyType my queries is:
select Player, HeroType, Level
from alphaTable
where HeroType = 'Guardian'

output would be:
[1] Nexius, Guardian, 1
[2] Thorar, Guardian, 3

select NPC, EnemyType, Level
from alphaTable
where EnemyType = 'Guardian'

output would be:
[1] Devastor, Guardian, 2
[2] Void Mage, Guardian, 4

I would like the table to look like so:

Player  Level   NPC         Level
---------------------------------
Nexius  1       Devastor    2
Thorar  3       Void Mage   4
Any ideas to get this in pure SQL?

Share on other sites
evolutional    1393
Are you trying to match on equal levels? What if there were an npc of the same or higher level? In SQL sever you could achieve it via subquery or cte and rank/join each result on row_number() for the set

Share on other sites
Alpha_ProgDes    6921
I would like all the Players of HeroType: Guardian to be listed on the left and all the NPCs of EnemyType: Guardian to be listed on the left.

Share on other sites
krez    443
I can't think of any way that isn't horribly ugly and specific to each different database system. But since this is in the Web Development forum, I'm guessing it is for display on a website. You could run two separate queries (or even one with a UNION of the two queries you want), and sort out the formatting in your display code (PHP or whatever you are using).

Share on other sites
Alpha_ProgDes    6921
Well I'm exclusively using SQL Server 2008, if that helps.

Share on other sites
leiavoia    960
Your table looks like it's trying to do two different things. It has bad "SQL smell" i guess you could say. Maybe i don't understand the objective, but i'm thinking it either needs to be two (or three?) tables for players, enemies, and npc's. However, if they are all somewhat similar, you might try something like:

Players {
INTEGER id, -- primary key
TEXT name, -- whatever you feel like
ENUM type, -- enemy / hero / npc
ENUM (or TEXT?) class, -- 'Guardian','Paladin','Mule Face Idiot', etc
INTEGER level
}

That would hold everybody in one table. Then you can just query by player type.

Why do you want your table output like you originally posted? What's the purpose of that?

Quote:
 I would like all the Players of HeroType: Guardian to be listed on the left and all the NPCs of EnemyType: Guardian to be listed on the left.

Yeah, that's a UNION, and no, you can't put them into two sides of a table. I can't think of a logical reason for doing that though. You'll have to format in script like krez said.

Share on other sites
evolutional    1393
Unions concatenate result sets, this is more about joining them. How are you going to consume these results? It does sound to me that two separate queries would be best, unless you want to get into full outer joins and so on

Share on other sites
krez    443
I'm making assumptions on your table structure and data (you only showed the results of a couple SELECTs, not the DDL or actual data), so this might have to be adjusted. Also, please please please don't do this; I feel dirty now just typing it. You should really rethink your database design. leiavoia gave you some good pointers, and don't mutilate your database just to save three minutes of work on getting the front end to be pretty.

CREATE TABLE alphaTable	(	Player		VARCHAR(20) NULL,	HeroType	VARCHAR(20) NULL,	Level		INT NOT NULL,	NPC		VARCHAR(20) NULL,	EnemyType	VARCHAR(20) NULL	);INSERT INTO alphaTable (Player, HeroType, Level) VALUES ('Nexius', 'Guardian', 1);INSERT INTO alphaTable (Player, HeroType, Level) VALUES ('Thorar', 'Guardian', 3);INSERT INTO alphaTable (NPC, EnemyType, Level) VALUES ('Devastor', 'Guardian', 2);INSERT INTO alphaTable (NPC, EnemyType, Level) VALUES ('Void Mage', 'Guardian', 4);SELECT	p.Player,	p.Level,	e.NPC,	e.LevelFROM	(	SELECT	ROW_NUMBER() OVER (ORDER BY Player) AS rid,		Player,		Level	FROM 	alphaTable	WHERE	Player IS NOT NULL	) p	LEFT OUTER JOIN (			SELECT	ROW_NUMBER() OVER (ORDER BY NPC) AS rid,				NPC,				Level			FROM	alphaTable			WHERE	NPC IS NOT NULL			) e		ON e.rid = p.ridORDER BY p.Player;

Basically it uses ROW_NUMBER to add an arbitrary integer column to the derived tables in te FROM clause (both of which are based on the original table but with different WHERE clauses to separate heroes and enemies), then JOINs them on that arbitrary column. It's a LEFT JOIN in case there are not the same number of enemies and heroes (otherwise some would get left out on one side or the other).

WARNING: This Code Causes Lung Cancer, Heart Disease, Emphysema, And May Complicate Pregnancy

Share on other sites
leiavoia    960
Quote:
 Original post by krez... Stench SQL ...

I felt dirty just reading that. You're corrupting the youth! :-)