November 22, 2010 at 3:21 pm
SELECT glbank.check_num
, glbank.ref_num
, glbank.check_amt
, glbank.check_date
, glbank.bank_code
, vendaddr.[name]
, vendor.vend_num
, vendor.vend_remit
FROM((aptrxp_all
INNER JOIN glbank
ON aptrxp_all.check_num=glbank.check_num)
INNER JOIN vendaddr
ON aptrxp_all.vend_num=vendaddr.vend_num)
INNER JOIN vendor
ON vendaddr.vend_num=vendor.vend_num
Now, I want to pull the correct vendor name based on whether the vendor.vend_num is null. If it is, then the vendor name will be pulled from vendor where it equals the vendor.vend_remit. In the vendor table there is only one name field and then the vendor number. One of the joined tables houses both the vendor number and remit number. If the remit number is not null then I pull the vendor name from the vendor table. If it is null, then the name will be based on the vendor number.
So basically if vendor.ven_num is not null then vendor.name = vendor.name where vendor.vend_num = vendor.vend_num else vendor.name = vendor.name where vendor.vend_remit = vendor.vend_remit...
So my dilemma is that I am not sure how to write a case statement or an if else statment among the joins. I hope that this helps.
November 22, 2010 at 3:44 pm
Sounds like you need to use a LEFT JOIN to the table that might be null, and an additional join to the other field/table. Select using COALESCE(table.column.that.might.be.null, value.to.use.if.other.is.null) for the value.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 22, 2010 at 4:38 pm
INNER JOIN vendor
ON (vendaddr.vend_num IS NOT NULL AND vendaddr.vend_num=vendor.vend_num)
OR (vendaddr.vend_num IS NULL AND vendaddr.vend_remit=vendor.vend_remit)
Scott Pletcher, SQL Server MVP 2008-2010
November 23, 2010 at 8:52 am
I appreciate the answers, but do not think that I would get the results that I am looking for...I've revised the code a bit, which of course does not work, to show what I am after, but can't seem to get the syntax and now I can't see the forest through the trees, LOL.
SELECT glbank.check_num
, glbank.ref_num
, glbank.check_amt
, glbank.check_date
, glbank.bank_code
, vendaddr.name
, vendor.vend_num
, vendor.vend_remit
FROM ((aptrxp_all
INNER JOIN glbank
ON aptrxp_all.check_num=glbank.check_num)
INNER JOIN vendaddr
ON aptrxp_all.vend_num=vendaddr.vend_num)
INNER JOIN vendor
ON vendaddr.vend_num=vendor.vend_num
If vendaddr.vend_num=vendor.vend_num Then
(SELECT vendaddr.name
FROM vendaddr
INNER JOIN vendor
ON vendaddr.vend_num=vendor.vend_num where vendaddr.vend_num=vendor.vend_num)
Else (SELECT vendaddr.name
FROM vendaddr
INNER JOIN vendor
ON vendaddr.vend_num=vendor.vend_remit where vendaddr.vend_num=vendor.vend_remit)
end
where glbank.check_amt= @amount
Again, thanks for all help.
November 23, 2010 at 8:58 am
Please try running this:
SELECT glbank.check_num
, glbank.ref_num
, glbank.check_amt
, glbank.check_date
, glbank.bank_code
, vendaddr.[name]
, vendor.vend_num
, vendor.vend_remit
FROM((aptrxp_all
INNER JOIN glbank
ON aptrxp_all.check_num=glbank.check_num)
INNER JOIN vendaddr
ON aptrxp_all.vend_num=vendaddr.vend_num)
INNER JOIN vendor
ON (vendaddr.vend_num IS NOT NULL AND vendaddr.vend_num=vendor.vend_num)
OR (vendaddr.vend_num IS NULL AND vendaddr.vend_remit=vendor.vend_remit)
WHERE glbank.check_amt= @amount
Scott Pletcher, SQL Server MVP 2008-2010
November 23, 2010 at 9:30 am
Yes, I see what you are trying to do, but the result is still not what I am looking for. I need the vendaddr.name and that is determined by either the vendor.vend_num or the vendor.vend_remit.
The thing is that the name is housed in the vendaddr table and its unique identifier is the vendaddr.vend_num. I am comparing the vendor.vend_num and the vendor.vend_remit against the vendaddr.vend_num. So you see, the vendaddr.vend_num is the VENDOR.vend_num and the VENDOR.vend_remit. So if the vendor.vend_num is null then the vendaddr.name is determined by VENDOR.vend_remit.
If it were a simple select statement, each one would like this:
select vendaddr.name
from vendaddr
inner join vendor
on vendor.vend_num = vendaddr.vend_num
where vendaddr.vend_num = 'BOX0001'
select vendaddr.name
from vendaddr
inner join vendor
on vendaddr.vend_num = vendor.vend_remit
where vendaddr.vend_num = 'INT0010'
I hope that this makes more sense...
November 23, 2010 at 9:46 am
It sounds as if you do need two LEFT OUTER JOINs, as Wayne suggested.
Other than that, sorry, I give up. Hopefully s/o else can help you.
Scott Pletcher, SQL Server MVP 2008-2010
November 23, 2010 at 11:48 am
Is this what you are looking for?
select vendaddr.name
from vendaddr
inner join vendor
on IsNull(vendor.vend_num, vendor.vend_remit) = vendaddr.vend_num
It will use the vendor_num if it is not null and will use the vend_remit otherwise.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 23, 2010 at 12:27 pm
Cheryl - try both options (conditional join or two left joins) and compare performance, preferably on a live or scaled-up test db. Usually the two (and only two, it usually loses with more) left joins will return your result set quicker. It will only take a few moments of your time to do this but it is always worth it.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 23, 2010 at 1:04 pm
Both to ChrisM and Drew. I appreciate the feedback.
To Drew:
However, the correct name is not pulling up. The original code modified using everyone's input:
SELECT glbank.check_num
, glbank.ref_num
, glbank.check_amt
, glbank.check_date
, glbank.bank_code
, vendaddr.name
, vendor.vend_num
, vendor.vend_remit
FROM ((aptrxp_all
LEFT JOIN glbank
ON aptrxp_all.check_num=glbank.check_num)
LEFT JOIN vendaddr
ON vendaddr.vend_num=aptrxp_all.vend_num)
LEFT JOIN vendor
on isNull(vendor.vend_num, vendor.vend_remit) = vendaddr.vend_num
WHERE glbank.check_amt= @amount
The results:
Check Ref Amount Date Code Name vend_num vend_remit
85772BOX0001-4935.280000002010-09-03 00:00:00.000BK1BOX USA BOX0001 INT0010
Based on the above results, the name should be based on the vendor.vend_remit , which means that the vendaddr.name is not Box USA, but International Paper. The vendaddr.name BOX USA would be the result if I was matching it up to the vendor.vend_num of BOX0001. Since the vend_remit is not null then it must pull the vendaddr.name where vendaddr.vendnum = vendor.vend_remit.
So in summary, if the vendor.vend_remit is not null then match that to vendaddr.vend_num to get the vendaddr.name. If it is null then use the vendor.vend_num and match it to vendaddr.vend_num to get the vendaddr.name.
I think that is where the confusion is coming. The vendaddr table holds the name and is identified by the vend_num in that table. the vendor table holds the vend_num and the vend_remit. This is important because this will determine who the check is made to...
November 23, 2010 at 1:34 pm
Cheryl McLaughlin-385812 (11/23/2010)
Both to ChrisM and Drew. I appreciate the feedback.To Drew:
However, the correct name is not pulling up. The original code modified using everyone's input:
SELECT glbank.check_num
, glbank.ref_num
, glbank.check_amt
, glbank.check_date
, glbank.bank_code
, vendaddr.name
, vendor.vend_num
, vendor.vend_remit
FROM ((aptrxp_all
LEFT JOIN glbank
ON aptrxp_all.check_num=glbank.check_num)
LEFT JOIN vendaddr
ON vendaddr.vend_num=aptrxp_all.vend_num)
LEFT JOIN vendor
on isNull(vendor.vend_num, vendor.vend_remit) = vendaddr.vend_num
WHERE glbank.check_amt= @amount
The results:
Check Ref Amount Date Code Name vend_num vend_remit
85772BOX0001-4935.280000002010-09-03 00:00:00.000BK1BOX USA BOX0001 INT0010
Based on the above results, the name should be based on the vendor.vend_remit , which means that the vendaddr.name is not Box USA, but International Paper. The vendaddr.name BOX USA would be the result if I was matching it up to the vendor.vend_num of BOX0001. Since the vend_remit is not null then it must pull the vendaddr.name where vendaddr.vendnum = vendor.vend_remit.
So in summary, if the vendor.vend_remit is not null then match that to vendaddr.vend_num to get the vendaddr.name. If it is null then use the vendor.vend_num and match it to vendaddr.vend_num to get the vendaddr.name.
I think that is where the confusion is coming. The vendaddr table holds the name and is identified by the vend_num in that table. the vendor table holds the vend_num and the vend_remit. This is important because this will determine who the check is made to...
Cheryl, I worked for you guys for four months in the spring and I ain't been paid yet 😀
You must get this right. Try complete overkill with left joins -
LEFT JOIN vendor v1
on v1.vend_num = aptrxp_all.vend_num -- note change from address table
LEFT JOIN vendor v2
on v2.vend_remit = aptrxp_all.vend_num
Get rid of those brackets in your FROM list: they look like they are there for a reason but they almost certainly aren't, they just confuse things for you.
FROM aptrxp_all
LEFT JOIN glbank
ON aptrxp_all.check_num = glbank.check_num
LEFT JOIN vendor v1
on v1.vend_num = aptrxp_all.vend_num -- note change from address table
LEFT JOIN vendaddr va1
ON va1.vend_num = v1.vend_num
LEFT JOIN vendor v2
on v2.vend_remit = aptrxp_all.vend_num
LEFT JOIN vendaddr va2
ON va2.vend_num = v2.vend_num
Be sure to look at values coming back from v1, v2, va1, va2.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 24, 2010 at 6:32 am
What do you mean you worked for us for 4 months in the spring? Sorry, don't recognize the name.
November 24, 2010 at 6:36 am
Cheryl McLaughlin-385812 (11/24/2010)
What do you mean you worked for us for 4 months in the spring? Sorry, don't recognize the name.
Cheryl McLaughlin-385812 (11/23/2010)
...This is important because this will determine who the check is made to...
😀
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply