Matching Two Different Columns With Two Records

[Tanner8]

Hey, I am trying to put together a MySQL Query to basically return one record when two different fields in two separate records match.

I have fields PostID (Primary Key), ThreadID, and Type in my database. I am trying to retrieve the last 5 records. Most of the time a simple query will do, but it needs to work with the constraint of if Type equals one of the other record's PostID, give me just the latest record.

I was trying to use MINUS to get a full result set, then remove all of the elements with with the Type being PostID, however I couldn't get it right because I couldn't compare the end result of one to the other.

Can anyone give me a hand?

Thanks

[Old Pedant]

Let me make sure I understand you:

PostID  ThreadID  Type
   101      37       4
   102      38       5
   103      43     101

So in this case, because PostID=103 has Type=101, you would want to show only PostID=103, because it is "later" (higher number) than 101??

Not hard.

SELECT T1.*
FROM table AS T1 LEFT JOIN table AS T2
ON T1.Type = T2.PostID
WHERE T2.PostID IS NULL
ORDER BY T1.PostID DESC
LIMIT 5

You could also do

SELECT * FROM table
WHERE PostID NOT IN ( SELECT Type FROM table )
ORDER BY PostID DESC
LIMIT 5

But usually the JOIN will be faster, esp. with MySQL.

It would help performance if you added an index (even a non-unique index) to the Type field.

***********

If I misunderstood you, please post again but show some sample data with expected results.

[Tanner8]

That worked great, thank you for the help. I would like to point out that there is a small error in your statement however. You must do "T2.Type=T1.PostID" as opposed to "T1.Type = T2.PostID". Thank you

[Old Pedant]

DOH! Sorry about that. Good catch.