MySQL: Need help with query

mysql> desc ticket; (ticket_id=PRI)
+-------------------------+---------------------------------------------------+
| Field                   | Type                                              |
+-------------------------+---------------------------------------------------+
| ticket_id               | bigint(20) unsigned                               |
| ticket_subject          | char(128)                                         |
| ticket_date             | datetime                                          |
| ticket_status           | enum('new','bounced','resolved','dead','Escalated'|
+-------------------------+---------------------------------------------------+

PHP:

mysql> desc thread;  (thread_id=PRI)
+---------------------+-----------------------------------+
| Field               | Type                              |
+---------------------+-----------------------------------+
| ticket_id           | int(11)                           |
| thread_id           | int(10) unsigned                  |
| thread_message_id   | char(255)                         |
| thread_date         | datetime                          |
| thread_subject      | char(128)                         |
| thread_from         | char(64)                          |
+---------------------+-----------------------------------+

PHP:

I have the above 2 tables which store emails.
The common column is "ticket_id".

I need to find emails (ticket_id) sent by the same sender (thread_from) and with the same subject (thread_subject) where the ticket_status=new.
In short, identify duplicate emails.
How can I do this ?

It seems too complicated for me as it requires using both tables and finding duplicate / triplicate records.

Please help.

Thank you.
Vai

 

no there is a simple query to find duplicate records

you want to delete duplicate records

Or you want to select unique records

Regards

Alex

 

Use DISTINCT in your query.

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

 

I need a list of Duplicate / Triplicate records so that I can review them and then delete them if required.

How to use UNIQUE with 2 tables.
I do not know how to use queries where 2 tables are involved.

A sample query on the above tables (to give the output as I have mentioned) will be of great help.

Thx

 

And hey, I need a list of duplicate records (not uniques).
Thx

 

Massage this query to your needs.



SELECT p1.fields, p2.fields
FROM table AS p1 INNER JOIN table AS p2 ON p1.lastname = p2.lastname
WHERE p1.id <p2.id AND

I am using < but you can use =

You want to use a DISTINCT on INNER join for this.

 

I could be totally off on this but something like the following:

select t1.ticket_id
from thread as t1, thread as t2, tickets
where t1.thread_from = t2.thread_from and t1.thread_subject = t2.thread_subject and t1.ticket_id = tickets.ticket_id and t1.ticket_id <> t2.ticket_id and tickets.ticket_status = 'new'

 

Yeah that's pretty much what I posted previously.

You went the extra mile and used his field names where I took the lazy route and showed him the logic.

Sometimes I do this so that people can learn on their own.

If you always copy/paste you never learn in my opinion.

 

I wasn't terribly sure I had set that up right, myself. Though typically I do agree...teach a man to fish and all.