Solved

Join Query

Posted on 2012-03-12
8
289 Views
Last Modified: 2012-03-12
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

0
Comment
Question by:dlavar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 167 total points
ID: 37710371
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?
0
 

Author Comment

by:dlavar
ID: 37710445
Yes it's only one value that = 14.  I removed the quotes and it's still pulling all of them.
0
 
LVL 9

Assisted Solution

by:macarrillo1
macarrillo1 earned 167 total points
ID: 37710530
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?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:dlavar
ID: 37710544
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.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37710549
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
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37710558
Try

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

Accepted Solution

by:
dqmq earned 166 total points
ID: 37710579
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
0
 

Author Comment

by:dlavar
ID: 37710655
I found out what my problem was.  It was an issue in my database.  Thanks for your help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question