CODEWITHSUNDEEP
mysql
Dog
Dog

Reputation: 2906

SQL INNER JOIN only returns the first result

I have a Many to Many relationship between Pets and Users, with a UserPets join table. I've been trying to write an SQL query that would return the user name with the amount of times walked. However, I've only been able to return a user and a timesWalked and they are not even the associated ones.

The current SQL statement I have is:

SELECT user_pets.timesWalked, users.name
FROM user_pets
    INNER JOIN users ON user_pets.user_id = users.id

And the returned result is:

[{"timesWalked"=>20, "name"=>"user1", 0=>20, 1=>"user1"}]

In this instance, user1 is not the one that walked the dog 20 times, which is the association I need. 

table name: users
name 
user1
user2
user3

table name: user_pets
name    timesWalked    userId   petId
usep1    10               2       1
usep2    20               1       3
usep3     5               1       2

table name: pets
name
pet1
pet2
pet3

My expected result is:

[{"timesWalked"=>20, "name"=>"user1"}]
[{"timesWalked"=>10, "name"=>"user3"}]
[{"timesWalked"=>5, "name"=>"user2"}]

In this instance, the user name from the User's table is being associated with timesWalked from the user_pets table

How can I return all the timesWalked with all the user names who walked the pet?

Upvotes: 0

Views: 97

Answers (1)

Udit Solanki
Udit Solanki

Reputation: 531

You can use inner join to achieve this. Below query lists all the users who have walked all the pets for total number of time. i.e. User 1 has walked two pets for 2 times each.

select
    up.timesWalked, u.name as UserName, p.name as PetName
from user_pets up 
inner join users u on up.user_id = u.id
inner join pets p on up.pet_id = p.id

Have a look at this fiddle : http://sqlfiddle.com/#!17/c91a1/2

Upvotes: 2

Related Questions