CODEWITHSUNDEEP
mysqlsql
Rafael Vidal
Rafael Vidal

Reputation: 333

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.

Upvotes: 1

Views: 34

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Related Questions