Only taking 1 entry from the other table

Question

I have 2 Tables that need to be joined, to illustrate here are my tables:

Table baby

    |id|name |action_id|
    |1 |John |1        |
    |2 |Jane |2        |

Table action

    |id|action|details|
    |1 |laugh |hihihi |
    |1 |laugh |hahaha |
    |2 |cry   |huhuhu |

What I have:

    |id|name |action_id|action|
    |1 |John |1        |laugh |
    |1 |John |1        |laugh |
    |2 |Jane |3        |cry   |

As I didn't need the details, here is what I want:

    |id|name |action_id|action|
    |1 |John |1        |laugh |
    |2 |Jane |3        |cry   |

Can someone help me make the right query?

Have you looked at the `DISTINCT` keyword? Just add it to the beginning of the query you already have. http://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html — Sablefoste, Dec 02 '15 at 06:36

Tim Biegeleisen · Answer 1 · 2015-12-02T06:57:58.923

0

Here is an ANSI-92 compliant answer to the question:

SELECT DISTINCT b.id, b.name, b.action_id, a.action
FROM baby b INNER JOIN action a ON b.action_id = a.id

edited Dec 02 '15 at 06:57

answered Dec 02 '15 at 06:40

Tim Biegeleisen

I thought my problem lies in the joining clause. Thus, I search only regarding the Join topic. Thanks a lot for the help! – Kelvin Dec 02 '15 at 06:46

score 0 · Accepted Answer · answered Dec 02 '15 at 06:42

0

As suggested by @drew, you can just use the following Query (you might want to confirm the names of your tables don't conflict with the field names):

SELECT DISTINCT baby.id, baby.name, action.action 
FROM baby
JOIN action
WHERE baby.action_id=action.id;

answered Dec 02 '15 at 06:42

Sablefoste

Hmmm.... normally I do. I guess the late hour and the rush to get an answer that works out lead me to this. An interesting read and opinions about it, however: http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 – Sablefoste Dec 02 '15 at 06:46
I thought my problem lies in the joining clause. Thus, I search only regarding the Join topic. Thanks a lot for the help @Sablefoste ! – Kelvin Dec 02 '15 at 06:47

2 Answers2