Link to home
Start Free TrialLog in
Avatar of Lia Nungaray
Lia NungarayFlag for United States of America

asked on

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!
ASKER CERTIFIED SOLUTION
Avatar of imitchie
imitchie
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lia Nungaray

ASKER

Thanks! Solution worked out great!