• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

Sub Select returning more than one value

The b_Payment table has a 1 to many relationshsip with the b_paymentSegments table.

I need to get the GenericCode and Amt from the b_paymentSegments table in my SELECT statement.

I think the below query show what I would like to see.  I just don't know if it can be done or what the correct syntax is.
SELECT n.claimId, CONVERT(varchar, n.serviceDate,1) as serviceDate, bp.amount, bp.amtPatientPaid,
(SELECT GenericCode, amount FROM b_paymentSegments WHERE GroupCode = 'CO' AND b_paymentId = bp.Id) AS COCode,
(SELECT GenericCode, amount FROM b_paymentSegments WHERE GroupCode = 'PI' AND b_paymentId = bp.Id) AS PICode

FROM fNoteBw(12, '01/01/2000', '12/31/2999') n 
INNER JOIN b_payment bp ON bp.b_claimId = n.Id 
LEFT JOIN b_paymentSegments ps ON ps.b_paymentId = bp.id

Open in new window

0
huerita37
Asked:
huerita37
1 Solution
 
Patrick MatthewsCommented:
Your subqueries are both returning two columns.  You can't do that; they eac need to return one column.

Perhaps you should explain what you are trying to do, and provide some sample data from the three tables and what the results should be based on that sample.
0
 
tim_csCommented:
You won't be able to do it that way since you're returning more than one value in the sub query.  Try this out.  
SELECT 
	n.claimId, 
	CONVERT(varchar, n.serviceDate,1) as serviceDate, 
	bp.amount, 
	bp.amtPatientPaid, 
	psco.GenericCode,
	psco.Amount,
	pspi.GenericCode,
	pspi.Amount
FROM 
	fNoteBw(12, '01/01/2000', '12/31/2999') n  
	INNER JOIN b_payment bp 
		ON bp.b_claimId = n.Id  
	LEFT JOIN b_paymentSegments psco
		ON psco.b_paymentId = bp.id 
		AND psco.GroupCode = 'CO'
	LEFT JOIN b_paymentSegments pspi
		ON pspi.b_paymentId = bp.id
		AND pspi.GroupCode = 'PI'

Open in new window

0
 
huerita37Author Commented:
I have one question.  Is it better to use the INNER JOINS or have a SELECT statement for each column.  For example:

",(SELECT GenericCode FROM b_paymentSegments WHERE GroupCode = 'CO' AND b_paymentId = bp.Id) AS coCode " & _
                        ",(SELECT amount FROM b_paymentSegments WHERE GroupCode = 'CO' AND b_paymentId = bp.Id) AS coAmt " & _
                        ",(SELECT GenericCode FROM b_paymentSegments WHERE GroupCode = 'PR' AND b_paymentId = bp.Id) AS prCode " & _
                        "(SELECT amount FROM b_paymentSegments WHERE GroupCode = 'PR' AND b_paymentId = bp.Id) AS prAmt " & _
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now