How to get rid of duplicate rows from a SQL join due to bad table design?-CodePudding

I'm adding 6 columns to my main table with a left join, but the right table has duplicates in the primary key column because it wasn't designed properly. I can't fix that so I have to work with I've got. I'm doing this in MS Access so I don't have access to things like Group By Partition.

This is my main table: | KeyID | | ------| | 1111 |

Table for the join:

KeyID	PA	QL
1111	X
1111		X

I want my final table to look like this:

KeyID	PA	QL
1111	X	X

How can I accomplish this?

CodePudding user response：

Not sure I understand the problem, but you can apply an aggregate function such as MAX to get rid of nulls:

SELECT t1.KeyID, MAX(t2.PA) AS PA, MAX(t2.QL) AS QL
FROM t1
LEFT JOIN t2
    ON t1.KeyID = t2.KeyID
GROUP  BY t1.KeyID;

CodePudding user response：

My idea isn't very scalable, because you'd have to make a new query for every column that you have this issue with, but here is how I would do it.

Make 3 queries:

[Distinct KeyID] only
main table WHERE PA IS NOT NULL
main table WHERE QL IS NOT NULL

Now the 4th query,

SELECT query1.KeyID, query2.PA, query3.QL
FROM query1
LEFT OUTER JOIN query2 ON query1.KeyID = query2.KeyID
LEFT OUTER JOIN query3 ON query1.KeyID = query3.KeyID

CodePudding user response：

You can use two subqueries:

SELECT 
    KeyID, 
    (Select PA 
    From YourTable As T 
    Where T.KeyID = YourTable.KeyID And PA Is Not Null) AS PA, 
    (Select QL 
    From YourTable As T 
    Where T.KeyID = YourTable.KeyID And QL Is Not Null) AS QL
FROM 
    YourTable
GROUP BY 
    KeyID;