CODEWITHSUNDEEP
sql
pyetjegoo
pyetjegoo

Reputation: 79

Query gives me duplicates at times

I have this issue with my search option. My problem is, it gives me duplicates. And it can't be, because if I search by ID it gives me two rows, and the ID is unique. This is the query I use:

SELECT forma.*, SMS_MONTIME.IDTICKET, SMS_MONTIME.MBYLLUR,SMS_MONTIME.time_added
FROM forma 
LEFT JOIN SMS_MONTIME ON forma.ID = SMS_MONTIME.IDTICKET where forma.$kategoria LIKE '%$search%' ORDER BY forma.ID

What can I use in order to avoid duplicates? Maybe not left join.

Upvotes: 0

Views: 2020

Answers (3)

Moha Dehghan
Moha Dehghan

Reputation: 18463

This happens when you have more that one row in SMS_MONTIME table with IDTICKET equal to a single forma.ID. For example if you have only one row in forma table with ID equal to 10, you may have two rows in SMS_MONTIME with IDTICKET equal to 10. Then you get two rows in the result, with duplicate information from forma table and distinct information from SMS_MONTIME table.

You cannot avoid this unless you rethink the result you want. What should happen to distict information of the SMS_MONTIME table? You may need to extract required information with separate queries, or you may process the result (with duplicates) to extract the reqired information.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

As mentioned in my comment, if you have multiple records in your SMS_MONTIME table, you're going to receive duplicates. If you only want to receive a single record, you can get the MAX(time_added) and just show that record. Here is an example:

SELECT forma.*, SMS_MONTIME.IDTICKET, SMS_MONTIME.MBYLLUR,SMS_MONTIME.time_added
FROM forma 
    LEFT JOIN (
        SELECT IDTICKET, MAX(time_added) max_time_added
        FROM SMS_MONTIME 
        GROUP BY SMS_MONTIME.IDTICKET
    ) SMS_MONTIME_MAX ON forma.ID = SMS_MONTIME_MAX.IDTICKET 
    LEFT JOIN SMS_MONTIME ON 
        forma.ID = SMS_MONTIME.IDTICKET AND SMS_MONTIME.time_added AND SMS_MONTIME_MAX.max_time_added
WHERE forma.$kategoria LIKE '%$search%' 
ORDER BY forma.ID

Hope this helps.

Upvotes: 0

exexzian
exexzian

Reputation: 7890

have you tried with DISTINCT

SELECT DISTINCT column_name(s)
FROM table_name
Where <conidition>

check this link

Upvotes: 0

Related Questions