We help IT Professionals succeed at work.

Join Query

dlavar
dlavar asked
on
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

Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:
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?

Author

Commented:
Yes it's only one value that = 14.  I removed the quotes and it's still pulling all of them.
Michael CarrilloInformation Systems Manager
Top Expert 2012
Commented:
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?

Author

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 Manager
Top Expert 2012

Commented:
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 Manager
Top Expert 2012

Commented:
Try

Select p.vchRecordID
      from tblAAPPayment p
      inner join tblOnlineAAPAdjusters a on a.vchConfirmNum = p.vchPaymentNum
      where a.intID = 14
Commented:
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

Author

Commented:
I found out what my problem was.  It was an issue in my database.  Thanks for your help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.