Solved

Join Query

Posted on 2012-03-12
8
286 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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