Need help with my query

[busboy]

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!

[r937]

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

[busboy]

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!

[r937]

yes, it might have helped if you had mentioned which database you're on, and that you are not on the current release

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

[busboy]

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?

[r937]

uh oh, my bad

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

[busboy]

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.

[r937]

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