4

Table1

Move1 Move2
Fire Ice
Grass Fire
Ice Grass

Table2

Move Power
Fire 40
Grass 30
Ice 20

I have these two tables, but when I run this SQL statement in WAMPServer, I get no rows returned:

SELECT *
FROM Table1, Table2
    INNER JOIN Table1 as Hello ON Hello.move1 = Table2.move
    INNER JOIN Table1 as Meow ON Meow.move2 = Table2.move

Both tables are under the same database, so I can't see my error. I want it to return something like...

Table1

Move1 Move2 Move Power Move Power
Fire Ice Fire 40 Ice 20
Grass Fire Grass 30 Fire 40
Ice Grass Ice 20 Grass 30

Where did I go wrong?

Nimantha
  • 6,405
  • 6
  • 28
  • 69
abanterful
  • 51
  • 3

2 Answers2

3

You're code is wrong in several ways, you should learn how to use SQL joins (This is a nice, basic explanation)

As far as solving your problem, try this:

SELECT *
FROM table1 INNER JOIN table2 t2_m1
  ON table1.move1 = t2_m1.move INNER JOIN t2_m2
  ON table1.move2 = t2_m2.move
Amit
  • 45,440
  • 9
  • 78
  • 110
2

This should work:

select
  m.move1,
  m.move2,
  m1.move,
  m1.power,
  m2.move,
  m2.power
from
  table1 m
  join table2 m1 on m1.move = m.move1
  join table2 m2 on m2.move = m.move2

You can test this in SQL Fiddle

James Z
  • 12,209
  • 10
  • 24
  • 44