CODEWITHSUNDEEP
mysqljoin
prograshid
prograshid

Reputation: 927

JOIN on multiple tables giving duplicate records - MySql

Here is list of my tables and necessary columns

users u .

screen_name,
country,
status

twitter_users_relationship tf. This table have multiple target_screen_name for each screen_name.

screen_name,
target_screen_name,
target_country,
follow_status

user_twitter_action_map ta 

screen_name,
action_name,
action_status

user_targeted_countries utc .This table have multiple countries for each screen_name 

screen_name,
country_name

I want to get all target_screen_name from twitter_users_relationship that have matched target_country with u.country or utc.country_name

My query so far

SELECT u.screen_name,
       u.country,
       tf.target_screen_name,
       tf.target_country,
       ta.action_name,
       ta.action_status,
       utc.country_name

FROM users u
 LEFT JOIN twitter_users_relationship tf
   ON u.screen_name=tf.screen_name
 LEFT JOIN user_twitter_action_map ta
   ON u.screen_name=ta.screen_name
   AND ta.action_name='follow'
   AND ta.action_status='active'
 LEFT JOIN user_targeted_countries utc
   ON u.screen_name= utc.screen_name
WHERE u.status = 'active'
  AND tf.follow_status = 'pending'
  AND tf.target_country != ''
  AND tf.target_country IS NOT NULL
  AND ( utc.country_name=tf.target_country OR u.country=tf.target_country)
  AND u.screen_name = 'my_screen_name';

But this query giving me duplicate record for each entry of countries in user_targeted_countries. If there are 3 counties in user_targeted_countries the it will return 3 duplicate records.

Please let me know what JOIN I need to use with user_targeted_countries to get desired results.

u.country can be different than countries in utc.country_name

UPDATE - If I removes OR u.country=tf.target_country from the WHERE clause then I get all the matched target_screen_name without duplicate. But I am not sure how to get all those records also that matches with u.country=tf.target_country ?

Upvotes: 0

Views: 34

Answers (1)

sagi
sagi

Reputation: 40481

Depends on the business logic required ..

First, regardless to the question, your query is wrong(Either the LEFT JOIN or the conditions) . When using LEFT JOIN , conditions on the right table should only be specified in the ON clause, which means you need to move all the conditions on tf. and utc. to the ON clause.

Secondly, you can use a GROUP BY clause and choose one of the utc.country_name (different answers will be if you want a specific one, if it doesn't matter, use MAX() on this column).

Upvotes: 2

Related Questions