SQL query returning two of each record

Question

Why is this query selecting two of each record and how can I make it only select one?

I don't believe it's related to the JSON selection, but could be.

SELECT 
    note.*, 
    usr.first_name AS usr_first_name, 
    usr.last_name AS usr_last_name, 
    e.data->>('f' || lname.field_id::text) AS entry_last_name, 
    e.data->>('f' || fname.field_id::text) AS entry_first_name
FROM note
LEFT JOIN usr ON note.usr_id = usr.usr_id
LEFT JOIN entry AS e ON e.entry_id = note.entry_id
LEFT JOIN field AS lname ON (lname.section_id = e.section_id AND lname.type = 'name')
LEFT JOIN field AS fname ON (fname.section_id = e.section_id AND fname.type = 'first_name' AND fname.enabled = 1)
WHERE note.grp_id = 1 AND note.deleted = 0 ORDER BY note.date DESC
LIMIT 20

Can you supply sample data and expected results? Are the records returned actually duplicated or are their differences in some of the columns? I presume adding distinct does not fix the issue. — sgeddes
– sgeddes, Commented May 13, 2015 at 0:42
It's returning two of the same exact result. Where would I add distinct? — Max Hudson
– Max Hudson, Commented May 13, 2015 at 0:43

Erwin Brandstetter · Accepted Answer · 2015-05-13 01:00:54Z

2

Most probably one of your LEFT JOIN tables has two rows for each row on the right. If you test with SELECT *, you should see differences in the resulting rows (unless you have complete duplicates in that table, which would be a problem).

The best way to fix it depends on information that's not available in the question. One way would be to join to a subselect (possibly a LATERAL join) that folds duplicates before joining so that a single row on the left is guaranteed. That's typically much more efficient than removing dupes with DISTINCT later.

edited May 13, 2015 at 1:00

answered May 13, 2015 at 0:56

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Hudson Over a year ago

Fixed it - There were multiple Left Joins

Collectives™ on Stack Overflow

SQL query returning two of each record

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related