July 5, 2013 at 4:43 am
Hi,
I have the below query to join several tables, and retrieve customer data:
select sv.SVSEQ, sc.SCACO, ac.f1 as AccountNo, ne.NEAN, ne.NEEAN,bg.BGCFN1,bg.BGCFN2, bg.BGCFN3, bg.BGCUS,
sv.SVNA1 ,sv.SVNA2, sv.SVNA3, sv.SVNA4, sv.SVNA5, sv.SVPZIP,sv.SVCSA, sc.SCACO,sx.SXDLM
from NEPF ne
inner join tmpAccountList ac on NEEAN=ac.f1
inner join BGPF bg on bg.BGCUS=ne.NEAN
inner join SCPF sc on sc.SCAN=bg.BGCUS and bg.BGCUS=ne.NEAN and sc.SCAN=LEFT(ac.f1,6)
inner join sxpf sx on sx.SXCUS=LEFT(ac.f1,6)
INNER JOIN SVPF SV ON sv.SVDLM=sx.SXDLM
my issue is with the table SVPF which has thousands of duplicate records.
There is a column SVSEQ which is numbered, but not uniquely. I want to join to that table, but only get one row for each individual Sequence in my results set.
Without that table, I have 150 records, with that table, I have several hundred thousand.
How can I add that table using the SVSEQ column without getting duplicates?
July 5, 2013 at 4:54 am
Can you post some sample data and desired output?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 5, 2013 at 4:56 am
select sv.SVSEQ, sc.SCACO, ac.f1 as AccountNo, ne.NEAN, ne.NEEAN,bg.BGCFN1,bg.BGCFN2, bg.BGCFN3, bg.BGCUS,
sv.SVNA1 ,sv.SVNA2, sv.SVNA3, sv.SVNA4, sv.SVNA5, sv.SVPZIP,sv.SVCSA, sc.SCACO,sx.SXDLM
from NEPF ne
inner join tmpAccountList ac on NEEAN=ac.f1
inner join BGPF bg on bg.BGCUS=ne.NEAN
inner join SCPF sc on sc.SCAN=bg.BGCUS and bg.BGCUS=ne.NEAN and sc.SCAN=LEFT(ac.f1,6)
inner join sxpf sx on sx.SXCUS=LEFT(ac.f1,6)
INNER JOIN (
SELECT
SVSEQ, SVDLM, SVNA1, SVNA2, SVNA3, SVNA4, SVNA5, SVPZIP, SVCSA,
rn = ROW_NUMBER() OVER (PARTITION BY SVSEQ ORDER BY SVDLM)
FROM SVPF
) SV ON sv.SVDLM = sx.SXDLM AND rn = 1
Experiment by running the subquery on it's own to determine the best column to order by.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 5, 2013 at 5:00 am
Without knowledge of the table it is impossible to give a good answer. You can always do:
JOIN (SELECT *,
row_number() OVER(PARTION BY SVSEQ ORDER BY (SELECT NULL)) AS rowno
FROM SVPF) AS SV ON SV.SVDLM = SX.SXDLM
AND SV.rowno = 1
It will reduced the number of rows, but it is very unlikely that the result is correct.
I think the correct answer is to speak with someone who knows these tables better to be able to find out which rows to present.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 5, 2013 at 5:05 am
Another guess: -
SELECT sv.SVSEQ, sc.SCACO, ac.f1 AS AccountNo,
ne.NEAN, ne.NEEAN, bg.BGCFN1, bg.BGCFN2,
bg.BGCFN3, bg.BGCUS, sv.SVNA1, sv.SVNA2,
sv.SVNA3, sv.SVNA4, sv.SVNA5, sv.SVPZIP,
sv.SVCSA, sc.SCACO, sx.SXDLM
FROM NEPF ne
INNER JOIN tmpAccountList ac ON NEEAN = ac.f1
INNER JOIN BGPF bg ON bg.BGCUS = ne.NEAN
INNER JOIN SCPF sc ON sc.SCAN = bg.BGCUS AND bg.BGCUS = ne.NEAN AND sc.SCAN = LEFT(ac.f1, 6)
INNER JOIN sxpf sx ON sx.SXCUS = LEFT(ac.f1, 6)
CROSS APPLY (
SELECT TOP 1 SVSEQ, SVDLM, SVNA1, SVNA2, SVNA3, SVNA4, SVNA5, SVPZIP, SVCSA
FROM SVPF a
WHERE a.SVDLM = sx.SXDLM
ORDER BY SVDLM
) SV;
July 5, 2013 at 7:40 am
thanks everyone, will get to work
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply