Sign in to follow this  
Alpha_ProgDes

[web] formatting two tables into one table

Recommended Posts

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 this post


Link to post
Share on other sites
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 this post


Link to post
Share on other sites
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 this post


Link to post
Share on other sites
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?

edit: sorry, didn't read

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 this post


Link to post
Share on other sites
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 this post


Link to post
Share on other sites
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.Level
FROM (
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.rid
ORDER 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 this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this