Retrieving one value from each set
-
Hi, folks. Well, I'm back again. This time I need help solving what one would think would be an easy problem, even in set logic.
I have tables that store customers phone numbers and another that stores their addresses.
I'll use the phone table as the example because the problem is the same for both tables.
A customer can have one or many phones stored in the phones table. But, I just need one phone number for each customer to present to my application user. However, a customer may or may not have a home phone, or a business phone, or a cell phone, or a fax phone, or whatever, that I can home in on. I have no guarantee a phone type will be in this table or not.
So, how do I get a *single* phone number for every customer from his or her set of phones in a phonebook? Again, set logic escapes me here. Nothing I've tried works.
I feel braindead on this one. I would never imagined it being this difficult for me.
It has to be easy, right? Thanks for any help,
Lee...
-
If it is not relevant in which order the phone numbers (home, ...) is preferred, left join this:
...
LEFT JOIN (
SELECT
customerid,
MAX(phonenumber) AS phonenumber
FROM
tbl_phonenumbers
GROUP BY
customerid
) AS tmp_phonenumbers ON tmp_phonenumbers.customerid = tbl_addresses.id
If you prefer lets say the home phone and when the home phone number is not present just take any other (lets say the tbl_phonenumbers has a field type and type=1 means the home phone number):
LEFT JOIN (
SELECT
customerid,
CASE
WHEN homephone IS NOT NULL THEN homephone
ELSE anyphone
END AS phonenumber
FROM (
SELECT
customerid,
MAX(homephone) AS homephone,
MAX(anyphone) AS anyphone
FROM (
SELECT
customerid,
MAX(phonenumber) AS homephone,
NULL AS anyphone
FROM
tbl_phonenumbers
WHERE
type = 1 -- 1 is home phone
GROUP BY
customerid
UNION ALL
SELECT
customerid,
NULL AS homephone
MAX(phonenumber) AS anyphone
FROM
tbl_phonenumbers
WHERE
type 1 -- not the home phone
GROUP BY
customerid
) AS tmp_phonenumbers1
GROUP BY
customerid
) AS tmp_phonenumbers2 ON tmp_phonenumbers2.customerid = tbl_addresses.id
-
>> If it is not relevant in which order the phone numbers (home, ...) is preferred, left join this: >> ...
That'll do it.
Sigh...I need to think through these things from a different perspective than I'm now coming from. I mean, I tried a couple of MINs and MAXs but could never get them to work right. Joins never entered my mind for this problem.
Experience, experience and more experience, I guess.
Thanks Michael.
Lee...
-
I actually kept working with the problem and stumbled on this solution, which is much simpler. Dunno how I missed it.
_____________________
SELECT min(number), id
FROM
phones
AS p
where
number > ''
group
by id
_____________________
Just an fyi...
Thanks again.
Lee...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply