Join Query

I have an inner join query that not's pulling the data that I need. It needs to pull one record where the int = 14 in the tblOnlineAAPAdjusters table.  But it's pulling all the record id's that have joined by vchConfirmNum and vchPaymentNum.  How can I get it to pull that one value?

Select vchRecordID
	from tblAAPPayment p
	join tblOnlineAAPAdjusters a on a.vchConfirmNum = p.vchPaymentNum 
	where a.intID = '14'

Open in new window

dlavarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
is a.intID an actual integer?  If so don't wrap it in quotes.

Select vchRecordID, a.intID
      from tblAAPPayment p
      join tblOnlineAAPAdjusters a on a.vchConfirmNum = p.vchPaymentNum
      where a.intID = 14 -- NO QUTOES


Also added the a.intID column for now to ensure you're not needing other filters.  Is there only one record where a.intID = 14?
dlavarAuthor Commented:
Yes it's only one value that = 14.  I removed the quotes and it's still pulling all of them.
Michael CarrilloInformation Systems ManagerCommented:
It sound like you have a one to many relationship between tblAAPPayment and tblOnlineAAPAdjusters .  How many records are you pulling? Also, the records that you pull do they have an intID of 14?

Select a.vchRecordID
      from tblAAPPayment p
      join tblOnlineAAPAdjusters a on a.vchConfirmNum = p.vchPaymentNum
      where a.intID = 14

which table does vchRecordID below to?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

dlavarAuthor Commented:
the RecordID belongs to tblAAPPayment.  tblOnlineAAPAdjusters doesn't have a vchRecordID, but it has the confirmation num which is the same as the paymentnum in the Payment table.
Michael CarrilloInformation Systems ManagerCommented:
What is the datatype of tblOnlineAAPAdjusters .vchConfirmNum  and
tblOnlineAAPAdjusters .vchPaymentNum?  


Try

Select a.vchRecordID
      from tblAAPPayment p
      inner join tblOnlineAAPAdjusters a on a.vchConfirmNum = p.vchPaymentNum
      where a.intID = 14
Michael CarrilloInformation Systems ManagerCommented:
Try

Select p.vchRecordID
      from tblAAPPayment p
      inner join tblOnlineAAPAdjusters a on a.vchConfirmNum = p.vchPaymentNum
      where a.intID = 14
dqmqCommented:
That's how join works-->the "14" is on the one side of a 1-many relationship.

If you have this:

Table A
X      13                  
Y      14
Z      15
       
Table P
Y       two
Y       three
Y       one


Then the result is this

Table A join P
Y 14 one
Y 14 two
Y 14 three

If you want to consolidate that to a single-row result, then you need to explain how you want to aggregate or eliminate the related values in the other table


Perhaps you could illustrate your desired outcome with a small amount of table data

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dlavarAuthor Commented:
I found out what my problem was.  It was an issue in my database.  Thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.