Solved

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

Posted on 2007-12-06
2
145 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 60
SQL Date from a string 4 58
Set the max value for a column 7 37
Delete from table 6 46
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

864 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now