Unable to merge these sql queries into one query-CodePudding

First Query:

select * 
from PLAYERS_DATA pd,PLAYERS_SERVICE ps 
where pd.ID=ps.ID

Second Query:

select * 
from PLAYERS p, PLAYERS_SERVICE ps 
where p.PLAYER_ID=ps.PLAYER_ID

Third Query:

select * 
from PLAYERS_PROPERTY pp, PLAYERS p 
where pp.PLAYER_ID=p.PLAYER_ID

My Attempt to join these 3 queries:

select distinct p.*,pp.*,pd.*
from PLAYERS p
    join PLAYERS_SERVICE ps on p.PLAYER_ID=ps.PLAYER_ID
    join PLAYERS_DATA pd on pd.ID=ps.ID
    join PLAYERS_PROPERTY pp on pp.PLAYER_ID=p.PLAYER_ID

With my query, the relation looks fine, but I am seeing duplicate records when I included different columns from different tables in select query. While I was using distinct also, able to see duplicate records. Can anyone please help me to identify the mistake and get matching distinct records for columns from different tables.

CodePudding user response：

Since you use the DISTINCT keyword, it is impossible to receive duplicate records. It is possible that there are partial duplicates (like a service having multiple data matches and property matches).

However, you also have duplicate fields if the same field is represented in multiple tables. To avoid duplicating your fields, it is a good idea to not select everything like

select distinct p.*,pp.*,pd.*

because it is unsafe (what if there is a password or other sensitive data that is unnecessary to your result) and superfluous (loading unnecessary fields), as well as increases resource usage, as all the unnecessary fields for all your records are loaded into memory without a need for it, which also drops performance.

You should list the exact columns you need instead of selecting everything from everything.

After you fix your fields, your DISTINCT might become more effective. If it's still not enough, then you will need to modify your join/where criteria according to your needs. If you need further information, then you need to provide:

which columns you need from which tables
sample data which reproduces the issue
explaining through an example or a pattern how the undesired duplicate manifests in your case

A SQL fiddle would not hurt either.