Solved

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

Posted on 2007-12-06
2
149 Views
Last Modified: 2010-04-21
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!
0
Comment
Question by:horalia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20423011
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
 

Author Closing Comment

by:horalia
ID: 31413248
Thanks! Solution worked out great!
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question