Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Aug 2009
    Posts
    51
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Matching Two Different Columns With Two Records

    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Let me make sure I understand you:
    Code:
    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.

    Code:
    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
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Tanner8 (11-14-2011)

  • #3
    New Coder
    Join Date
    Aug 2009
    Posts
    51
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    DOH! Sorry about that. Good catch.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •