Relating two tables, one which has more than one row per key in other table
Posted on 2007-12-06
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
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!