Selecting only one row in a 1-to-many data set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting only one row in a 1-to-many data set

Author

Topic

Jules_S
Starting Member

13 Posts

Posted - 2009-05-07 : 11:11:52

I have a situation where I need to produce a result set that spans data in two tables. The relationship is a one-to-many, where there may be one or more rows in table B that match with the primaruy key value from a unique row in table A.

The actual circumstance is a customers table (main table A) with a contacts table (table B), where there may be more than one contact for the customer. I need to be able to select only one of the contacts (doesn't actually matter which one) for each customer. Example:

Table A
-------

Customer ID Customer Name
----------- -------------
1 Fred Bloggs Ltd
2 Blue Moon plc

Table B
-------

Customer ID Contact Name Contact Phone No
----------- ------------ ----------------
1 Bob Smith 01234 567890
2 Lindsay White 09999 999999
2 Martin Jones 07788 990011

I need to create a result set that looks like:

Customer ID Customer Name Contact Name Contact Phone No
----------- ------------- ------------ ----------------
1 Fred Bloggs Ltd Bob Smith 01234 567890
2 Blue Moon plc Lindsay White 09999 999999

I don't care which one of the contact names gets selected for customer ID 2. The important thing is that there must only be one row in the result set per customer ID.

I've tried searching through old posts for examples but I can't find anything that seems to match this requirement. I need to keep this as simple as possible - I'm not new to SQL but I'm certainly no guru! Anyone point me in the right direction?

Jules

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-07 : 12:44:52

[code]SELECT a.CustomerID,a.CustomerName,b.[Contact Name], b.[Contact Phone No]
FROM TableA a
CROSS APPLY (SELECT TOP 1 [Contact Name], [Contact Phone No]
FROM TableB
WHERE CustomerID=a.CustomerID
ORDER BY NEWID()) b
[/code]

Jules_S
Starting Member

13 Posts

Posted - 2009-05-08 : 09:49:01

Apologies - I should have mentioned that this is a SQL2000 database....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 09:54:57

well you posted in a SQL 2005 forum

try this ..


select a.CustomerID, a.CustomerName, b.ContactName, b.ContactPhoneNo
from   TableA a
       inner join TableB b on  a.CustomerID  = b.CustomerID
       inner join
       (
           select CustomerID, ContactName  = max(ContactName)
           from   TableB
           group by CustomerID
       ) c                 on  a.CustomerID  = c.CustomerID
                           and b.ContactName = c.ContactName

if you have a PK in TableB, use that instead of max(ContactName)

KH
[spoiler]Time is always against us[/spoiler]

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-08 : 09:57:57

SELECT a.[Customer ID],a.[Customer Name],c.[Contact Phone No], (SELECT TOP 1 [Contact Name] FROM [tableB] b WHERE b.[Customer ID] = a.[Customer ID]) as [Contact Name] FROM [tableA] a INNER JOIN [tableB] c ON a.[Contact Name] = c.[Contact Name]

Jules_S
Starting Member

13 Posts

Posted - 2009-05-08 : 10:46:17

Thanks for all your help guys - much appreciated. My apologies for posting in the wrong forum - because I'm forced to use SQL Management Studio for everything rather than Enterprise Manager / QA I tend to forget which server is which!

Jules

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:17:53

no problems see below to check what server you're using at any time

http://support.microsoft.com/kb/321185

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-05-09 : 05:22:32

Hi Visakh16,

Why are you using NEWID() in Order by clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 05:29:08

quote:
Originally posted by aprichard

Hi Visakh16,

Why are you using NEWID() in Order by clause

for getting a random row. NEWID() generates a random GUID value each time so ordering by it gives a random row from related table which is what OP asked for.

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-05-09 : 05:34:29

Thanks Visakh,

I have table with Five Fields.

which will give high performace among the two statement.

select * from a

select f1, f2, f3, f4, f5 from a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources