August 14, 2013 at 1:17 pm
Hello all, I was wondering if someone can help me with an issue that I'm having. I'm trying to retrieve 1 fax number on a join. Here's the scenerio.
1. I have a Vendors table that has the Vendor ID in it.
2. I have a Address_Phone table that contains phone numbers with phone types such as FAX, BUS, etc. In this table it also has a POS (position) column that tells what position that phone number is in. A FAX number can be in any position 1,2,3.. So, in my scenerio I can have 3 record where I have a BUS phone number in POS 1 (First Record) then a FAX number in POS 2 (Second Record) and another FAX number in POS 3 (Third Record).
I'm doing a left outer join because a Vendor may not have a fax number.
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM FROM VENDOR V LEFT OUTER JOIN ADDRESS_PHONE AP ON
V.VENDOR_ID = AP.VENDOR_ID WHERE ADDRESS_PHONE_TYPE = 'FAX'
The problem is that if the VENDOR ID is in ADDRESS_PHONE table more than once (which it is) with more than 1 fax number it brings me back both fax numbers. I only want one; but I'm don't know what position this fax number will be in because it can be in any position if one exists.
Any thoughts?
August 14, 2013 at 1:20 pm
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 14, 2013 at 2:08 pm
Here is my DDL and DML statements:
CREATE TABLE VENDOR
(
VENDOR_ID INT PRIMARY KEY,
VENDOR_NAME VARCHAR(10)
)
GO
CREATE TABLE ADDRESS_PHONE
(
VENDOR_ID INT,
POS INT,
ADDRESS_PHONE_NUM VARCHAR(12),
ADDRESS_PHONE_TYPE VARCHAR(3)
)
INSERT INTO VENDOR SELECT 1, 'VENDOR1'
GO
INSERT INTO VENDOR SELECT 2, 'VENDOR2'
GO
INSERT INTO ADDRESS_PHONE SELECT 1, 1, '333-888-4444', 'BUS'
INSERT INTO ADDRESS_PHONE SELECT 1, 2, '333-555-1111', 'FAX'
INSERT INTO ADDRESS_PHONE SELECT 1, 3, '333-444-0000', 'FAX'
August 14, 2013 at 2:13 pm
Excellent. My first thought would be pretty much the query you posted in your original post. However you said you want to return only 1 of them. Which one would you want returned and what is the rule for that?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 14, 2013 at 6:34 pm
If you're after the one with the lowest POS, this should do it:
SELECT VENDOR_ID, ADDRESS_PHONE_NUM
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
WHERE ADDRESS_PHONE_TYPE = 'FAX'
) a
WHERE rn=1
On the other hand, if you want to return all vendors including those without a FAX number, as seems to be indicated by the LEFT JOIN you're doing, you'd need to do it like this:
SELECT VENDOR_ID, ADDRESS_PHONE_NUM, rn
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
WHERE ISNULL(ADDRESS_PHONE_TYPE, 'FAX') = 'FAX'
) a
WHERE rn=1
Note: The ISNULL makes the query non-SARGable.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 15, 2013 at 6:13 am
Thanks everyone for their help. That last solution really helped out a lot. I'm gratefully appreciated.
🙂
August 15, 2013 at 6:14 am
Thanks, dwainc your post really helped.
August 15, 2013 at 7:49 am
I'm not sure if this will perform better, but it's another way of getting the information you need.
SELECT V.VENDOR_ID,
ADDRESS_PHONE_NUM
FROM VENDOR V
OUTER APPLY( SELECT TOP 1 ADDRESS_PHONE_NUM
FROM ADDRESS_PHONE AP
WHERE V.VENDOR_ID = AP.VENDOR_ID
AND ADDRESS_PHONE_TYPE = 'FAX'
ORDER BY POS) P
August 15, 2013 at 7:51 am
dwain.c (8/14/2013)
If you're after the one with the lowest POS, this should do it:
SELECT VENDOR_ID, ADDRESS_PHONE_NUM
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
WHERE ADDRESS_PHONE_TYPE = 'FAX'
) a
WHERE rn=1
On the other hand, if you want to return all vendors including those without a FAX number, as seems to be indicated by the LEFT JOIN you're doing, you'd need to do it like this:
SELECT VENDOR_ID, ADDRESS_PHONE_NUM, rn
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
WHERE ISNULL(ADDRESS_PHONE_TYPE, 'FAX') = 'FAX'
) a
WHERE rn=1
Note: The ISNULL makes the query non-SARGable.
Dwain,
To avoid the ISNULL, just change the WHERE for an AND.
SELECT VENDOR_ID, ADDRESS_PHONE_NUM
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
AND ADDRESS_PHONE_TYPE = 'FAX'
) a
WHERE rn=1
August 15, 2013 at 8:32 pm
Luis Cazares (8/15/2013)
dwain.c (8/14/2013)
If you're after the one with the lowest POS, this should do it:
SELECT VENDOR_ID, ADDRESS_PHONE_NUM
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
WHERE ADDRESS_PHONE_TYPE = 'FAX'
) a
WHERE rn=1
On the other hand, if you want to return all vendors including those without a FAX number, as seems to be indicated by the LEFT JOIN you're doing, you'd need to do it like this:
SELECT VENDOR_ID, ADDRESS_PHONE_NUM, rn
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
WHERE ISNULL(ADDRESS_PHONE_TYPE, 'FAX') = 'FAX'
) a
WHERE rn=1
Note: The ISNULL makes the query non-SARGable.
Dwain,
To avoid the ISNULL, just change the WHERE for an AND.
SELECT VENDOR_ID, ADDRESS_PHONE_NUM
FROM (
-- Your query (reformatted)
SELECT V.VENDOR_ID, ADDRESS_PHONE_NUM
-- But with a row number added
,rn=ROW_NUMBER() OVER (PARTITION BY V.VENDOR_ID ORDER BY POS)
FROM VENDOR V
LEFT OUTER JOIN ADDRESS_PHONE AP
ON V.VENDOR_ID = AP.VENDOR_ID
AND ADDRESS_PHONE_TYPE = 'FAX'
) a
WHERE rn=1
+1 for that!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 16, 2013 at 1:56 pm
Here is another way. And I'll caveat this by saying that I am in no way implying it is any better or worse than what has already been provided (indeed I don't know if it is). I'm simply offering it as another solution that can be helpful at times. For example, we have a table with around 100M rows in it, with an account number and timestamp column that are part of the clustered index. If I try to put ROW_NUMBER() in there like Dwain did, it will take significantly longer than If I use the method here. Just a thought. (and it is a DB2 database, so maybe all bets are off there.)
SELECT
a.vendor_id, a.vendor_name, b.address_phone_num, b.address_phone_type
FROM vendor a LEFT OUTER JOIN address_phone b
ON a.vendor_id = b.vendor_id
AND b.pos =
(SELECT MIN(pos) FROM address_phone c
WHERE b.vendor_id = c.vendor_id
AND address_phone_type = 'FAX')
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 16, 2013 at 5:40 pm
Thanks Greg, greatly appreciate it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply