Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Join Query

Posted on 2012-03-12
8
Medium Priority
?
295 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 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 668 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 668 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 664 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

876 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