SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Need help with my query

    Two tables are refereneced below:

    1. contacts = con
    2. communication = com

    SELECT con.date, con.contactID, con.firstName, con.lastName, con.address1, con.address2, con.city, con.state, con.zip, con.country, con.phone, con.needs, com.details FROM contacts con, communication com WHERE con.confirmed = 'Yes' and con.approved = 'Yes' and con.needsLabel = 'Yes' and con.contactID = com.contactID order by con.contactID;

    The problem here is that the communication table may have several entries for a given contactID. I need to somehow extract the most recent entry. A subquery that comes to mind is:

    SELECT details from communication where contactID = '$contactID' order by communicationID desc limit 1;

    So how do I somehow merge the two of these queries?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select con.date
         , con.contactID
         , con.firstName
         , con.lastName
         , con.address1
         , con.address2
         , con.city
         , con.state
         , con.zip
         , con.country
         , con.phone
         , con.needs
         , com.details 
      from contacts con
    inner
      join communication com 
        on con.contactID = com.contactID 
     where con.confirmed = 'Yes' 
       and con.approved = 'Yes' 
       and con.needsLabel = 'Yes' 
       and com.communicationID 
         = ( select max(communicationID)
               from communication
              where contactID = con.contactID ) 
    order 
        by con.contactID
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rudy, unfortunately when I plug in what you provided I get the following error:

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(communicationID) from communication where contactID

    I double checked to make sure there weren't any stray characters that may have messed it up. Is there any other info I can provide that may help?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, it might have helped if you had mentioned which database you're on, and that you are not on the current release



    Code:
    select con.date
         , con.contactID
         , con.firstName
         , con.lastName
         , con.address1
         , con.address2
         , con.city
         , con.state
         , con.zip
         , con.country
         , con.phone
         , con.needs
         , com.details 
      from contacts con
    inner
      join communication com 
        on con.contactID = com.contactID 
    inner
      join communication com2 
        on con.contactID = com2.contactID     
     where con.confirmed = 'Yes' 
       and con.approved = 'Yes' 
       and con.needsLabel = 'Yes' 
    group
        by con.date
         , con.contactID
         , con.firstName
         , con.lastName
         , con.address1
         , con.address2
         , con.city
         , con.state
         , con.zip
         , con.country
         , con.phone
         , con.needs
         , com.details 
    having com.communicationID 
         = max(com2.communicationID)
    order 
        by con.contactID
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rudy, now I get this when I run it:

    #1054 - Unknown column 'com.communicationID' in 'having clause'

    By the way, I used to live in Toronto too near the Spadina subway exit. Where are you at?
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    uh oh, my bad
    Code:
    select con.date
         , con.contactID
         , con.firstName
         , con.lastName
         , con.address1
         , con.address2
         , con.city
         , con.state
         , con.zip
         , con.country
         , con.phone
         , con.needs
         , com.details 
         , com.communicationID
      from contacts con
    inner
      join communication com 
        on con.contactID = com.contactID 
    inner
      join communication com2 
        on con.contactID = com2.contactID     
     where con.confirmed = 'Yes' 
       and con.approved = 'Yes' 
       and con.needsLabel = 'Yes' 
    group
        by con.date
         , con.contactID
         , con.firstName
         , con.lastName
         , con.address1
         , con.address2
         , con.city
         , con.state
         , con.zip
         , con.country
         , con.phone
         , con.needs
         , com.details 
    having com.communicationID 
         = max(com2.communicationID)
    order 
        by con.contactID
    i'm in east york
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    844
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, that did the trick. So you must be using something later than MySQL 4.0.22. The first way you posted looks easier.
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    actually, i'm on 4.0

    i've just been writing sql for 18 years so i know the easier way, despite not being able to run it on my version of mysql

    being on an older release is kinda fun too, because it makes you seek alternate solutions to problems that are easy with derived tables and subqueries
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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