Relating two tables, one which has more than one row per key in other table

I have one table named contact, which contains the the contact information from a user. This table contact, can have many rows for just one user, and some columns may be empty depending on what information what updated. How do I create a select statement which will select the contact_number field which is not equal to '' (apparently this particular column does not store nulls) and having the maximum modified date? For example....

select user.user_id, contact.contact_number
from user
join contact on user.user_id = contact.user_id
having max(contact.modified_date) and contact.contact_number <> ''

Any suggestions will be greatly appreciated. Thanks!
horaliaAsked:
Who is Participating?
 
imitchieCommented:
select user.user_id,
  (select top 1 contact.contact_number from contact
    where user.user_id = contact.user_id
     and contact.contact_number <> ''
    order by modified_date desc) as contact_number
from user
0
 
horaliaAuthor Commented:
Thanks! Solution worked out great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.