CODEWITHSUNDEEP
mysql
Jayrox
Jayrox

Reputation: 4375

MySQL Query giving duplicates

I am having trouble getting my query to return unique results.

users_systems db setup:
int id
int user_id
int system_id
int mic`

example users_systems data:
id user_id system_id mic
1 1 1 1
2 1 3 0

users_gamernames db setup

int id
int user_id
int system_id
varchar user_name

example users_gamernames data
id user_id system_id user_name
1 1 1 jayr0xx
2 1 3 jayrox

Query: 

SELECT DISTINCT users_systems.system_id, users_systems.mic users_gamers.user_name
FROM users_systems  
LEFT JOIN users_gamernames ON users_gamernames.user_id = users_systems.user_id  
WHERE users_systems.user_id =4  
LIMIT 0 , 30

which returns:
system_id mic user_name
1 0 jayrox
1 1 jayr0xx
3 0 jayrox
3 1 jayr0xx

which i want to return:
system_id mic user_name
1 1 jayr0xx
3 0 jayrox

this is driving me insane
i am writing this in PHP 5.3 (PDO)

Upvotes: 2

Views: 122

Answers (2)

Bee
Bee

Reputation: 12502

Natural Joint is what you have to use here.

SELECT DISTINCT system_id, mic ,user_name FROM users_systems NATURAL JOIN users_gamernames

Upvotes: 0

Kendrick
Kendrick

Reputation: 3787

LEFT JOIN users_gamernames ON (users_gamernames.user_id = users_systems.user_id and users_gamernames.system_id = users_systems.system_id)

Because you left out the second join condition, you're getting the results for each user/system paired with each gamername.

Upvotes: 2

Related Questions