CODEWITHSUNDEEP
postgresqlduplicatesleft-joingreatest-n-per-group
Sami
Sami

Reputation: 492

Duplicate row after left join

I am trying to write a query which as follow:

select distinct  bsg.id as bsgId,
s.system_id as sysId,
g.code_no as gameNo,
u.user_name as nameOfUser,
s.score_code as scoreId,
p.name as cityOfGame

from score s
join scoreGr sg on sg.id = s.scoreGr_id
join bigScoreGr bsg on sg.bigScoreGr_id = bsg.id
join game g on bsg.fld_case_id = g.id
join user u on s.user_id = u.id
join system_number sn on g.id = sn.game_id
join system_doc sd on sd.system_number_id = sn.id
left join parameter p on sd.city_id = p.id

Until I have joined with parameter table, result is as expected. The result seems like below:

bsgId| sysId | gameNo | nameOfUser | scoreId
--------------------------------------------------
1234 | abcde | G-12   | admin      | G-12/1/1
1235 | abcdf | G-15   | admin      | G-15/1/3
1234 | abcdf | G-12   | user1      | G-12/1/8
1237 | abcdf | G-16   | user1      | G-16/2/4

However, parameter table is something big and system_doc has some null values in its city_id column. When I add the left join part of my query, it becomes like that:

bsgId| sysId | gameNo | nameOfUser | scoreId  | city
--------------------------------------------------
1234 | abcde | G-12   | admin      | G-12/1/1 | city1
1235 | abcdf | G-15   | admin      | G-15/1/3 | city5
1235 | abcdf | G-15   | admin      | G-15/1/3 | 
1234 | abcdg | G-12   | user1      | G-12/1/8 | city4
1234 | abcdg | G-12   | user1      | G-12/1/8 | 
1237 | abcdf | G-16   | user1      | G-16/2/4 |

I do not want rows like 3rd and 5th ones. To avoid these rows which has null in their city columns and "has the exact same data except city field" (I mean city can be null actually,as in the last row, but having row #2 makes row #3 useless, so I only want row #2) I have used distinct on(scoreId), but it did not worked since I have lost row #2 but not row #3.

How could I eliminate those duplicate rows which has null in their city fields? I hope my question is clear.

Upvotes: 3

Views: 4976

Answers (2)

dimka
dimka

Reputation: 1

It seems like you have a composite key. Try to mention all columns of composite key i.e. if you have primary key(pk1, pk2) then select * from table1 left join table2 on table1.pk1=table2.pk1 and table2.pk2=table2.pk2

Upvotes: 0

pbug
pbug

Reputation: 43

It's a postgresql bug.

left join parameter p on sd.city_id = p.id

Try this

left join parameter p on p.id = p.id
WHERE sd.city_id = p.id

(I have answered this so anyone looking for will now know about this bug)

Upvotes: 1

Related Questions