CODEWITHSUNDEEP
mysql
Muhammad Usman
Muhammad Usman

Reputation: 387

INNER JOIN Duplicate Records

My Question & Problem

I want to get two tables records at once. My SQL Query is:

SELECT r.upliner,r.refer,r.date_time,u.username,u.name,u.sponsor
FROM referral_details r 
INNER JOIN userdetails  u ON r.upliner = u.sponsor
WHERE u.sponsor = '1122'
GROUP BY u.username

This works fine and I'm not getting duplicate records, but the problem is that every row has some unique values like date_time but when I run the above query I'm not getting date_time unique.

referral_details Table Structure

upliner refer date_time
1122 2233 89-444-555
1122 3344 90-555-666
1122 4455 91-666-777
1122 5566 92-777-888
1122 6677 93-888-999
1122 7788 94-999-111

userdetails Table Structure

id username name code sponsor
1 usman Muhammad Usman 1122
2 arslan Jackson 2233 1122
3 rehman robo 3344 1122
4 talha mike 4455 1122
5 mehmod pompie 5566 1122
6 tafsil gita 6677 1122

When I run the above query I'm Getting this result

upliner refer date time sponsor username name
1122 2233 89-444-555 1122 arslan Jackson
1122 2233 89-444-555 1122 mehmod pompie
1122 2233 89-444-555 1122 rehman robo
1122 2233 89-444-555 1122 tafsil gita
1122 2233 89-444-555 1122 talha mike

As you can see I'm getting only usernames unique but their registered date_time is the same in all columns.

Expected Results

upliner refer date time sponsor username name
1122 2233 89-444-555 1122 arslan Jackson
1122 3344 90-555-666 1122 mehmod pompie
1122 4455 91-666-777 1122 rehman robo
1122 5566 92-777-888 1122 tafsil gita
1122 6677 93-888-999 1122 talha mike

in Expected Result, every user has its original stored value Like DateTime.

How can I achieve this? Please help me

Upvotes: 0

Views: 2456

Answers (1)

Qirel
Qirel

Reputation: 26460

I don't understand why you are using GROUP BY u.username, it doesn't quite make sense here.

However, you will get some duplicate records if you remove it - and that's because you INNER JOIN on just one of the fields, while I'm assuming it should be on both the referrer and the "upliner". If you join on just the one, it will match all the "upliner" of 1122 for every user. If you also join on the users code, you should be getting the result you are looking for.

SELECT r.upliner,
       r.refer,
       r.date_time,
       u.username,
       u.name,
       u.sponsor
FROM referral_details AS r 
INNER JOIN userdetails AS u 
    ON r.upliner = u.sponsor
    AND r.refer = u.code
WHERE u.sponsor = '1122'

Upvotes: 1

Related Questions