CODEWITHSUNDEEP
mysqlsqloracle-sqldeveloper
Ofer
Ofer

Reputation: 5219

How to get good results from DB

i have a problem with sql query. I need to get list of users. The problem is the left join i use. this is my query

 SELECT  
u.*  
 FROM  
users u LEFT JOIN game g on g.user_id = u.user_id  
LEFT JOIN game_actions ga on ga.game_id = g.id  
LEFT JOIN emails e on e.id = ga.email_id
WHERE  
u.user_id = 0  
AND u.is_contact_by_email = 1 
    AND email.type = 2

this query returns the same user more then once because of the join with other tables i want this query to return each user just one time. i'm using sql developer.

thanks in advanced.

Upvotes: 1

Views: 91

Answers (4)

Oriesok Vlassky
Oriesok Vlassky

Reputation: 797

I suppose there are more games, actions, and emails for each user. How do you imagine the result table to look like? Because when you are selecting only from user table (SELECT u.*), then there is no point in joining others. If you need the other tables, use GROUP BY (u.user_id) to group rows by user.

Upvotes: 0

aretai
aretai

Reputation: 1641

Try to use keyword distinct with your query

Upvotes: 0

Devart
Devart

Reputation: 121952

You may group result by user_id and use aggregate functions to return other fields, for eaxmple - 

SELECT u.user_id, GROUP_CONCAT(g.game_id) games FROM users u
  LEFT JOIN game g
    ON g.user_id = u.user_id  
  LEFT JOIN game_actions ga
    ON ga.game_id = g.id  
  LEFT JOIN emails e
    ON e.id = ga.email_id
WHERE
  u.user_id = 0  AND u.is_contact_by_email = 1
GROUP BY
  u.user_id

Upvotes: 1

Daniel Renshaw
Daniel Renshaw

Reputation: 34187

Given that you're not using game, game_actions, or emails for any purpose (they're not filtering users, and you're not enriching the results with data from either of these tables), there's no need to join those tables at all:

SELECT  
  u.*  
FROM users u
WHERE u.user_id = 0  
  AND u.is_contact_by_email = 1

Upvotes: 1

Related Questions