CODEWITHSUNDEEP
mysqlselectdistinct
user974435
user974435

Reputation: 397

Avoid double entry in SELECT query MYSQL

How to avoid multiple records to be listed on following query?

SELECT DISTINCT application_forms.date_submitted, 
                application_forms.application_type, 
                application_forms.online_offline2, 
                application_forms.decision, 
                application_forms.id, 
                notes.post_by_id 
FROM    application_forms 
             LEFT  JOIN notes 
                 ON  notes.user_id = application_forms.id

I would like to have 1 record listed not duplicates

Upvotes: 0

Views: 286

Answers (1)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can do it like this

SELECT      application_forms.date_submitted, 
            application_forms.application_type, 
            application_forms.online_offline2, 
            application_forms.decision, 
            application_forms.id, 
            n.post_by_id 
FROM    application_forms 
         LEFT  JOIN (SELECT user_id , MAX(id) , notes.post_by_id FROM notes) as n
             ON  n.user_id = application_forms.id  
GROUP BY application_forms.date_submitted

See in the left join MAX(id) chooses the very last record of each note and joins it to the outer query row

Upvotes: 1

Related Questions