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
Solved

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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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