'Select and join returning duplicate data

I have some tables that can be accessed here and I would like to get a new table with EntryId from Entry table and ProtocolNumber from JudicialOrder table. For that I'm using this query:

SELECT DISTINCT ET.EntryId, JOA.ProtocolNumber FROM Entry AS ET
LEFT JOIN JudicialOrderAccount AS JOT ON JOT.AccountId = ET.OwnerAccountId
INNER JOIN JudicialOrder AS JOA ON JOA.JudicialOrderId = JOT.JudicialOrderId;

But the ProtocolNumber is duplicated, what could be wrong with my query?

sql


Solution 1:[1]

As Kurt said only the combination of ET.EntryId, JOA.ProtocolNumber is unique. You will recognize it if you add an order by.

SELECT DISTINCT ET.EntryId, JOA.ProtocolNumber FROM Entry AS ET
LEFT JOIN JudicialOrderAccount AS JOT ON JOT.AccountId = ET.OwnerAccountId
INNER JOIN JudicialOrder AS JOA ON JOA.JudicialOrderId = JOT.JudicialOrderId
ORDER BY ET.EntryId, JOA.ProtocolNumber;

If you would really like to have unique protocol number you would need to group by ProtocolNumber and wrap EntryId in some string_agg function (depends on your database).

FYI: Your LEFT JOIN - INNER JOIN combination ends up being two INNER JOINs, see

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 claus

Related Questions