asked on

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

Patrick Matthews

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.

ASKER CERTIFIED SOLUTION

tim_cs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacque Scott

ASKER

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 " & _