Rafael Vidal Rafael Vidal - 6 months ago 10
SQL Question

Retrieving data from many tables with simple output

If I have tables with similar structure (with same columns), but with different info, can I retrieve it's data in the same output as a simple MySQl query (

SELECT * FROM table where Name="Boo";
)?

Ex.:

SELECT * FROM Weapon, Armor, Helm, Bomb, Potion where Name="Boo";


RESULT

ID Name Position.X Position.Y Item TableName
23 Boo 3 0 Gun (Weapon)
11 Boo 2 1 Cloth (Armor)
74 Boo 0 4 Tunic (Armor)
119 Boo 7 5 Coif (Helm)
9 Boo 1 6 Crown (Helm)
81 Boo 3 2 Grenade (Bomb)
44 Boo 8 1 Grenade (Bomb)
32 Boo 0 6 Small (Potion)
98 Boo 6 4 Big (Potion)


I have a system with set of tables (like 8) which, in some cases have the exactly same structure, and in others it's slightly different (one or two different columns).

Each table stores info about a user items and where they're stored.

I tried
INNER JOIN
, but it messed the results.

Answer

If the tables Weapon, Armor, Helm, Bomb, Potion have all the same structure You can use Union

  SELECT * FROM Weapon where Name="Boo" 
  union  
  SELECT * FROM Armor where Name="Boo"         
  union 
  SELECT * FROM Helm where Name="Boo"       
  union  
  SELECT * FROM Bomb where Name="Boo"       
  union  
  SELECT * FROM Potion  where Name="Boo" ;     

Otherwise you can set the proper column name for each select. In union clause the column must are of the same type and in the same number

Comments