CODEWITHSUNDEEP
mysql
user2491321
user2491321

Reputation: 673

mysql query returns only one results from my database

I have 2 tables in my database. Table users and table profile. 

users(user_id, surname, email)
profile(profile_id, country, user_id)

user_id in table profile, is FK (comes from users table). I have the following query in order to select all surnames "smith" from my database, that are from country "USA". This is my query:

SELECT u.name, 
u.surname, 
u.email, 
u.user_id,
p.user_id
FROM users u 
INNER JOIN profile p ON p.country = 'USA'
WHERE u.surname = 'smith' AND u.user_id = p.user_id

this query works fine, but the problem is that returns only 1 result and not all results from my database (people with surname smith that are from USA). Any idea where it might be the wrong and how to correct it?

Upvotes: 1

Views: 48

Answers (2)

juergen d
juergen d

Reputation: 204924

Use only the columns in your join on condition that are relevant for the join. The columns that link 2 tables together. And put the rest in the where clause. That should work:

SELECT u.name, 
       u.surname, 
       u.email, 
       u.user_id,
       p.user_id
FROM users u 
INNER JOIN profile p ON u.user_id = p.user_id
WHERE p.country = 'USA'
AND u.surname = 'smith'

This is also better for readability. You can read it like this:

SELECT these columns from table users. JOIN also table profile ON this 2 columns. After that filter the data and return only the records WHERE these conditions are met.

Upvotes: 0

Nishu Tayal
Nishu Tayal

Reputation: 20880

You should put the u.user_id = p.user_id condition with ON condition because you want to apply JOIN on 'user_id' field. And where clause should have the remaining condition.

SELECT u.name, 
       u.surname, 
       u.email, 
       u.user_id,
       p.user_id
FROM users u 
INNER JOIN profile p ON u.user_id = p.user_id
WHERE u.surname = 'smith' And p.country = 'USA'

You can read about INNER JOINS

Upvotes: 1

Related Questions