limiting left joined rows

[TravisLaborde]

Please help if you can, with this question:

I have a table with "people" information, and a table with "people contact records." For simplicity, let's say that the people contact records are all email addresses.

I have it broken into a separate table, so that each person may have multiple email addresses listed. With a one-to-may relationship between the tables.

This works fine, but for some business purposes, there are times when we need to return a resultset including each person (once) and their "email address." Meaning, that if this person has more than one email address, we only want "the first one." We really don't care if it is the FIRST one, the LAST one, whatever, just if they have one, show it....

I am using a LEFT JOIN, but in cases where the person has multiple email addresses, of course I am getting multiple rows in my resultset. In order to limit this to returning one row per person, I have added a condition to my JOIN.. ON clause... which checks if the key = (select min(key) where ....) In effect using my join condition twice. It seems to me like there must be a better way.

Can anyone help with this?

Thanks,
Travis

 

[swampBoogie]

select people.name,min(email)
  from people join peopleContact
    on people.pk = peopleContact.fk
 group by people.name

 

[KraGiE]

SELECT
u.NameOfUser,
Email = (SELECT Top 1 EmailAddress From UserData Where [UserID] = u.[UserID])
FROM
User u
Order By
u.NameOfUser

GO


.NET Developer/Contracting Consultant.
<a href=&quot;

http://www.inklass.com&quot;>InKlaSs.com</a>

 

[SemperFiDownUnda]

what swampBoogie and KraGiE are showing you but are not explaining is that you can limit just 1 record back but what you need to do is come up with the business logic of what is the customer's default email. Lets looks at a situation with something people are more used to. Customers with multiple phone numbers. Normally these Phone numbers are classified themself. So you might have a Business, Mobile, Home, Fax, Pager... numbers. Your boss wants a list of customers with their primary contact phone. How do you determine that? That depends on your business logic. Sure you could just pick the TOP 1 but what if that is the FAX....does the boss little good to call that and get a buzzing in his ear. So you might build in a priority classification in to the Phone table and grab the min or max record depending on how you impliment it.
If you haven't thought about these issues then you should. If you randomly pick one of the emails out of the list (and if you don't have the business logic to classify them then that is exactly what you are doing.) then I'd say you need to look at it because if you have my information in your system with 5 different emails one for my Accounting Dept, Shipping, My personal email, Webmaster, General contact, etc and you boss asks for emails of all customers and you query happens to pull an email that is the Webmaster or some other obscure one to send important info to and I don't get it in a timely maner your boss might not be a happy camper.

 

[TravisLaborde]

Good explanations, all, but I see that I was unclear in my question I do indeed have &quot;types&quot; of contact records, where a phone is not just a phone, but is a &quot;home phone&quot; versus an &quot;office phone&quot; etc...

As a matter of fact, that is what is making it so confusing for me. With a simple &quot;one-field&quot; situation like phone, I understand completely the concept of:

select people.somefield, phone = (select top 1 phone from details where ....)

My question lies around the fact that I thought the above usage was not as good as:

select people.somefields, details.phone from people left join details on....

And, with the JOIN syntax (is it better or not?), I am having trouble wrapping my mind around getting multiple fields, as in a mailing address. Like:

select people.name, details.addr1, details.addr2, details.addrcity, details.addrstate, details.addrzip from people left join details on details.person = people.id and (what would I put here to get the top 1 record of a certain type????)

Thanks,
Travis

 

[SemperFiDownUnda]

oh then its easy. You've got the database supporting what you need you just need to have narrow down your where result set.

Now you can either do this in your where in clause if you have a INNER JOIN or if use a LEFT OUTER JOIN i suggest putting it in the actual join as

SELECT ...
FROM Customer C
INNER JOIN CusomterPhones CP
ON C.CustID = CP.CustID
AND CP.PhoneType = 3

This will give you all customers and fill in the CustomerPhone columns only for PhoneType of 3

Now this will return you just 1 row per customer IF in CustomerPhones the combination of CustID and PhoneType is unique in the table .... ie Bob Smith is not allowed to have 2 Business phone numbers.

JOINS are generally more efficient than sub queries and corrilated sub queries

 

[r937]

yes, but...

FROM Customer C
INNER JOIN CusomterPhones CP
ON C.CustID = CP.CustID
AND CP.PhoneType = 3

what if there is no PhoneType 3? then no phone is selected

good syntax, not so good result, if there are other phone types for that customer

:-(

 

[SemperFiDownUnda]

well....what is the business rule you want to be applied?
Perhaps give the table def for both tables and sample of data for 1 customer from both tables (ie the 1 parent record and all the phone entries for that customer....make sure the data is fictional and phone numbers are 555 numbers 8)

With this i can modify my query to always return a phone number if atleast 1 is present BUT always return the phone number with the highest priority. Right now i'm just providing examples.

I did a similar thing at my last job where on a summary screen we brought up a contact phone number for a person if there was a working hour phone number than that was displayed, if not the Working hours mobile phone was displayed.... down the chain. And they had 2 foriegn keys for the phone....1 was the type of phone number the other was working hours, non working hours (something I argued against)

 

[TravisLaborde]

That last example from SimperFiDownUnda was pretty much exactly the way it is set up on my site. And, there are allowed to be multiple numbers of the same type, which is why I need &quot;just one&quot; of them. Meaning, each person may have multiple &quot;office numbers&quot; recorded. For this particular project, we just want any one of those.

And again, I think I do understand how to get just one, with a single-field like phone... but I get confused when I try to apply the same logic to something with multiple fields, like a mailing address.

For example, a person may have multiple mailing addresses, which are 5 fields (addr1, addr2, city, state, and zip). So, I need to join to that table, to find one &quot;record&quot; in the join, and then select those 5 fields from that one record.

Travis

 

[r937]

with multiple phone numbers of type 3, i think you can use min(phone) in the left join with type=3 in the join condition, and don't forget to group by

for addresses, min(xxx) is clumsier, but you can use it if xxx is a concat of all the address fields

there's gotta be an easier way, though

rudy

 

[TravisLaborde]

Rudy, that's EXACTLY what I'm getting at. I understand using group by and min(), but since I need multiple fields, I'm just having trouble wrapping my mind around it.

Thanks for helping to clarify my situation

Travus

 

[SQLSister]

YOu have an id field on the crecord don't you? If so then find the first record that matches the user and the other business logic you need and selct the fields based on that id field.

 

[r937]

SQLSister, that would be a correlated subquery? if so, how do you make it work in a LEFT OUTER scenario, with 5 fields?

my head hurts just thinking about it...

 

[TravisLaborde]

SQLSister, yes I do. But, just like r937 before me, my trouble is wording the LEFT JOIN to only get that one row.

Travis

 

[SemperFiDownUnda]

you still haven't told me the business rule really.

If your business rule is &quot;Pick one of the office numbers for this person at random&quot; then fine
the fact that you want multiple columns doesn't matter but you can do it via a join or sub query...easiest to visualise with a subselect

SELECT C.CustomerName,
CA.Street1,
CA.Street2,
CA.Street3,
CA.City,
CA.State,
CA.ZIP
FROM Customers C
LEFT OUTER JOIN CustomerAddress CA
ON C.CustomerID = CA.CustomerID
AND CA.AddressType = 3 --3 is a &quot;Office Address&quot;
WHERE CA.CustAddressID = ( SELECT MIN(CustAddressID)
FROM CustomerAddress
WHERE CustomerID = C.CustomerID
AND AddressType = 3 )

Now this will pull the first Office Address that was entered to the system most likely (depending on how actually add the data)

 

[TravisLaborde]

SemperFiDownUnda, what you wrote is exactly what I'm doing, so in a way, I'm glad to see it, but... It shows exactly what I was &quot;hoping&quot; to avoid. In the TSQL that you wrote, the &quot;main&quot; select statement has a JOIN, with conditions (ON this=that AND somethingelse=somethingelse), followed by a WHERE clause which further uses the same exact join conditions, in effect typing in the same conditions twice.

If I had been more clear in my original post, and typed up some SQL in my example exactly as you did, I guess I could have saved everyone some time. I apologize for that, though I do appreciate everyone's help.

But then, just to make sure I'm understanding this properly: the syntax that SemperFiDownUnda listed, is that the &quot;preferred&quot; method of doing this? I've had very little real training in this area, and I'm learning it from books and usage, and to me, it just &quot;felt&quot; wrong, perhaps because procedural programming is where most of my experience comes from.

Thanks again!
Travis

 

[SemperFiDownUnda]

This is where, if you had a column in your table, to indicate the priority of an address of phone number in relation to the others for a given Customer and address or phone type, you would not need the corrilated sub query. You could just put it in your join. With out this there is no way to identify, that I can think of, one out of a group specified in a join without a sub query to narrow it down.

Basically you have to narrow it down to a unque record and if you don't build that business functionally into your table then you have to do tricks that randomly do a similar function. I say random agian because I stress that you have no way of knowing before hand what phone number or address is going to be returned if that person has multiple records of the same type.

 

[TravisLaborde]

OK, thanks. We do have a &quot;primary&quot; designation, but it is for each contact type, meaning: primary phone number, primary email address, primary mailing address. Because, a given person may have 2-3 phone numbers listed, but one of them is the one that we &quot;mainly&quot; use to contact that person. We don't break it down further, into &quot;primary HOME number&quot; versus &quot;primary WORK number.&quot;

Thanks again, I really appreciate all the input everyone has made.

Travis