0

I have a table A which looks like this:

|name|ID|
---------
|cow | 1|
|dog | 1|
|cat | 2|
|bird| 2|
|pig | 2|

And I have a table B:

|action|ID|
-----------
|eat   | 1|
|drink | 2|
|go    | 3|

And I want my result-table to look like this:

|name|ID|action|
----------------
|cow | 1|eat   |
|dog | 1|eat   |
|cat | 2|drink |
|bird| 2|drink |
|pig | 2|drink |

I tried something like:

SELECT * FROM A
LEFT JOIN B ON A.ID=B.ID

But that resulted in many more lines than table A had. Every other join seems to be wrong too.

What is the way to do it?

4 Answers4

1

You want an inner join, left join includes all rows from table A and only matching details from B, inner join ensures its mutually exclusive so

SELECT * FROM A INNER JOIN B ON A.ID=B.ID
RF1991
  • 2,037
  • 4
  • 8
  • 17
Daniel Machet
  • 615
  • 1
  • 5
  • 7
0

An INNER JOIN on between two tables get only gets the results the exists in both tables, a LEFT JOIN will get the results that exist in the left (the table in the from statement) and the ones that match in the joined table. If you want to learn the differences, here is a good post about them.

This query will work for you:

SELECT
    a.name,
    a.id,
    b.action
FROM A a
INNER JOIN B b ON a.id = b.id;
dbajtr
  • 2,024
  • 2
  • 14
  • 22
-1

I Think this will work for you

SELECT name ,ID, (select action from B where ID=ID)as (action) FROM A

Anurag_Soni
  • 542
  • 2
  • 17
-1

you can try

SELECT *  FROM A, B
where A.ID = B.ID

OR 

SELECT * FROM B
LEFT JOIN A ON B.ID=A.ID
tungck
  • 9
  • 2