SQL Nastiness

cgarvey

If anyone is a dab hand at SQL could ya help me out here plz....

3 tables :
1. Person (first name, last name, etc)
2. Contact (first line, second line, area code, phone number, and all things related to any type of contact, Address, Email, Tel)
3. Contact Lookup (relates the person to the contact and tells us what type of contact it is (url, email, tel, etc))

What I want is one row per person with first name (from 1st table), first line & second line (from contact lookup table), but these two columns repeated 3 times, one set for each of 3 different types as defined in the second "Contact" table.

What I have now is a series of RIGHT OUTER JOINs
Person outer join contact-lookup (where type is first type i want) outer join contact (matched by the contact-lookup), again outer join contact-lookup (where type is second type i want) and so on ..

This works fine (might be sloppy!!), except that if a person has more than one contact of type XYZ (they will have more than 1 record in both contact and contact-lookup table for the type XYZ, and that returns 2 (or more) records for that person, whereas I only want 1 record with first/any in the column for type XYZ .. ie just gimme the first telephone number you see for this guy.

So the expected returned result set might be
FNAME | LNAME | LINE1 (HomeLine1) | LINE2 (HomeLine2) | LINE1 (WorkLine1) | LINE2 (WorkLine2) where the cols either have values if there was a contact for that person or NULL if not.

So how can I limit it to one record ("the first") per person.
I can post/pm the entire SQL I have now if thats of any help.

TIA
.cg

jd

I'm a bit confused..
you may want to do a select with a group by clause on one of the tables...before you do a join..I'll read your post again :rolleyes:

cgarvey

I don't blame you.

I guess what I want (what I really really want) is ...

Table A
id
fname
lname

Table B
aid (foreign key to A.id)
add1
add2

Give me all records in Table A and outer join Table B. That works just dandy assuming there is 0 or 1 records in B partaining to Table A's id. The problem arises when there are 2 records in Table B related/partaining to Table A. I then get 2 records in the resultset for that one party. What I want is one record in the result set per person (table A) regardless of 0,1 or more records in table B. If there are related records in table B then return only one (any one at all).

So if Table A was
1 Joe Soap
2 Peter Mark
3 George Bush

and B was
1 dublin ireland
3 washingtondc usa
3 texas usa

Then what I want returned is ..
1 Joe Soap dublin ireland
2 Peter Mark NULL NULL
3 George Bush washingtondc usa

if i get that sorted then i can expand/apply to my case (hopefully!)

.cg

jd

Originally posted by cgarvey
I don't blame you.



Give me all records in Table A and outer join Table B. That works just dandy assuming there is 0 or 1 records in B partaining to Table A's id. The problem arises when there are 2 records in Table B related/partaining to Table A. I then get 2 records in the resultset for that one party. What I want is one record in the result set per person (table A) regardless of 0,1 or more records in table B. If there are related records in table B then return only one (any one at all).



and B was
1 dublin ireland
3 washingtondc usa
3 texas usa

Then what I want returned is ..
1 Joe Soap dublin ireland
2 Peter Mark NULL NULL
3 George Bush washingtondc usa

if i get that sorted then i can expand/apply to my case (hopefully!)

.cg

What type of dbms are you using ?

off the top of my head, you have some criteria for selecting from b ...what is a primary or candidate key for B?

cgarvey

Originally posted by jd

What type of dbms are you using ?

Ooops forgot that ... MS SQL Server 2000

off the top of my head, you have some criteria for selecting from b ...what is a primary or candidate key for B?

None there can be but that won't enter the sql equation. the look up will be b.aid=a.id

ta

.cg

DeVore

What made you pick "washington" for the third guy rather then "texas" which seems just as reasonable a selection.

(If you tell me its because its "first" in the table I will BEAT you SEVERELY with an ORDER BY clause. I've a pet peeve about people who talk about the "first" record without specifying an order )

DeV.

cgarvey

Originally posted by DeVore
What made you pick "washington" for the third guy rather then "texas" which seems just as reasonable a selection.

Nothing! I thought you were getting into a heated political debate as to whether Bush spends his time in the office or at home!!

The point I made earlier was that I just wanted one row .. I couldn't give a <insert word of one's choosing> which row it was as long as it is just one and not both. It would be nice to have the first, but that's not a requirement.


.cg

jd

Originally posted by cgarvey


It would be nice to have the first, but that's not a requirement.


.cg

AAAAAAAAAAAAAAAAA
Devore is going to throw the book (of relational theory) at you....

cgarvey

ok so if you want to get all theoretical again .. go back to my first posting with the third (lookup) table..

A
=
id
fname
lname

B
=
id
add1
add2

C
=
lookupid
aid
bid

Now I have...
A
1 Joe Soap
2 Peter Mark
3 George Bush

B
1 dublin ireland
2 washingtondc usa
3 texas usa

C
1 1
3 2
3 3

What I want returned remains unchanged ..
1 Joe Soap dublin ireland
2 Peter Mark NULL NULL
3 George Bush washingtondc usa

(Theory ... pch!!)

jd

Siimplest thing, add an identity column as the primary key..
select min(identitycol) from B group by aid


this can be joined with B again on identitycol..

and then you do your outer join on this..
sorry more used to oracle so I'm not going to make a fool of myself writng the sql (if I haven't already)

cgarvey

Originally posted by jd
Siimplest thing, add an identity column as the primary key..
select min(identitycol) from B group by aid

That'll do it .. well a combination of sub selects and your select min() .. ta very muchly indeed.

.cg