Need help with tricky SQL SELECT problem

Question

Need help with tricky SQL SELECT problem

Orblin McLoon

SSC Veteran

Points: 276
More actions
August 18, 2005 at 10:47 pm

#89509

I need an SQL statement that will return a single row from two tables. The problem is there is a 1 to many (max 5) relationship between the tables.
Here are the tables:
CONTACT
ContactID (Unique primary key)
ContactName

CONTACTPOINT
ContactPointID (unique primary key)
ContactID (non-unique, up to 5, matches ContactID in CONTACT table)
ContactPoint

I basically need a single row returned that looks something like this:
contactID,ContactName,Contactpoint1,ContactPoint2,Contactpoint3....

There will be up to 5 Contactpoints.

Is this possible?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MidwestDBA SSC Veteran Points: 299 More actions · Answer 1

Here you go friend. Right out of BOL, I made the join and used max instead of sum. Sorry for the crappy formatting, but I am tired

Prelim stuff

create table CONTACT(

ContactID int,

ContactName varchar(10))

create table CONTACTPOINT (

ContactPointID int,

ContactID int,

ContactPoint varchar(10))

insert into Contact values (1, 'Dan')

insert into Contact values (2, 'Joe')

insert into ContactPoint values (1, 1, 'Joe')

insert into ContactPoint values (2, 1, 'Bob')

insert into ContactPoint values (3, 1, 'Fred')

insert into ContactPoint values (4, 1, 'Ron')

insert into ContactPoint values (5, 1, 'Ed')

insert into ContactPoint values (4, 2, 'Bob')

insert into ContactPoint values (3, 2, 'Fred')

insert into ContactPoint values (2, 2, 'Ron')

insert into ContactPoint values (1, 2, 'Ed')

select

a.ContactName,

max(CASE b.ContactPointID WHEN 1 THEN b.ContactPoint ELSE '' END) AS Contact1,

max(CASE b.ContactPointID WHEN 2 THEN b.ContactPoint ELSE '' END) AS Contact2,

max(CASE b.ContactPointID WHEN 3 THEN b.ContactPoint ELSE '' END) AS Contact3,

max(CASE b.ContactPointID WHEN 4 THEN b.ContactPoint ELSE '' END) AS Contact4,

max(CASE b.ContactPointID WHEN 5 THEN b.ContactPoint ELSE '' END) AS Contact5

from

Contact a

inner join

ContactPoint b

on a.ContactID = b.ContactID

group by

a.ContactID,

a.ContactName

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 2

You could simplify:-

SELECT TOP 1 *

FROM Contact C

INNER JOIN ContactPoint CP ON C.ContactID = CP.ContactPointID

But, just out of interest - why would you want to only return 1 row when there are multiple matches?

Have fun

Steve

We need men who can dream of things that never were.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

And to add to that, why should one particular row be returned as opposed to any other one of those 5 possiblities?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

MidwestDBA SSC Veteran Points: 299 More actions · Answer 4

Guys, I believe is asking to return a "pivot" result.

The statement "SELECT TOP 1 *

FROM Contact C

INNER JOIN ContactPoint CP ON C.ContactID = CP.ContactPointID" does not return a pivot. It returns the top record using an incorrect join.

The results from the query is using is that he gets all relevent data is returned in one row. This is very helpful e.g. Let say you have a quarterly report, your users do not want to see:

Item Quarter Quarter Number

Donut 25.00 1

Donut 50.00 2

Donut 35.00 3

Donut 47.00 4

Instead they want to see this

Item Quarter 1 Quarter 2 Quarter 3 Quarter 4

Donut 25.00 50.00 35.00 47.00

mmmmmm donuts....I am outta here.