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[web] formatting two tables into one table
i have a table that has columns Player, HeroType, Level, NPC, EnemyType
my queries is:
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
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.
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).
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
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.
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.
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
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.
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
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
Quote:Original post by krez
... Stench SQL ...
I felt dirty just reading that. You're corrupting the youth! :-)
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
