   [web] formatting two tables into one table
  i have a table that has columns Player, HeroType, Level, NPC, EnemyTypemy queries is:select Player, HeroType, Levelfrom alphaTablewhere HeroType = 'Guardian'output would be: [1] Nexius, Guardian, 1[2] Thorar, Guardian, 3select NPC, EnemyType, Levelfrom alphaTablewhere EnemyType = 'Guardian'output would be:[1] Devastor, Guardian, 2[2] Void Mage, Guardian, 4I would like the table to look like so:Player  Level   NPC    Level---------------------------------Nexius  1  Devastor    2Thorar  3  Void Mage   4Any ideas to get this in pure SQL?   
  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).   ;  Well I'm exclusively using SQL Server 2008, if that helps.   ;  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 keyTEXT name, -- whatever you feel likeENUM type, -- enemy / hero / npcENUM (or TEXT?) class, -- 'Guardian','Paladin','Mule Face Idiot', etcINTEGER 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 readQuote: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.CREATE TABLE alphaTable(PlayerVARCHAR(20) NULL,HeroTypeVARCHAR(20) NULL,LevelINT NOT NULL,NPCVARCHAR(20) NULL,EnemyTypeVARCHAR(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);SELECTp.Player,p.Level,e.NPC,e.LevelFROM(SELECTROW_NUMBER() OVER (ORDER BY Player) AS rid,Player,LevelFROM alphaTableWHEREPlayer IS NOT NULL) pLEFT OUTER JOIN (SELECTROW_NUMBER() OVER (ORDER BY NPC) AS rid,NPC,LevelFROMalphaTableWHERENPC IS NOT NULL) eON 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! :-)   