Solved

sql select problem - subquery returned too many values

Posted on 2006-07-14
2
443 Views
Last Modified: 2008-03-10
I have 2 tables Customers and Transactions.
Customers has a ref for each customer called InternalRef. We have a Supporter that also has an InternalRef as well as a SupportersRef which is linked back to Customers.InternalRef. That means they support that Customer.
A transaction can be made by both a Customer and a Supporter. If a Customer makes a transaction entry then their InternalRef is stored in the Transactions table. If a Supporter makes a Transaction entry then the supporters internal ref from the customers table is stored as a SupportersRef in the transactions table as well as the InternalRef of the Customer who they are supporting.

In both cases the customers InternalRef is stored in the Transactions table as InternalRef. Only if a supporter makes a transaction do we have an entry in Transactions.SupportersRef.

Customers Table:
InternalRef  Name SupportersRef   CustomerType
123             Jim    null                   Customer
124             Fred  123                   Supporter

Transactions Table:
InternalRef   SupportersRef   Amount
123              null                  10
123              124                  20

Customer Jim would see 2 transactions and supporter Fred would only see 1 transaction (the one with supportersref 124 in the Transactions table).

I want to return the 2 transaction for Jim - 1 with the name Jim and 1 with the name Fred, so that the customer knows where the transactions originated from.

Sample Jim sees the following:
Name       Amount  
Jim          10
Fred        20

Fred sees the following:
Name       Amount
Jim           20

I need the solution for Jim only as Fred is working fine.

Sample Query that works for Supporter Fred:

IF @CustomerType = 'Supporter'
BEGIN
select Transactions.InternalRef,Transactions.TransactionDate,Transactions.Amount,   Transactions.Description,Customers.SchoolOrSupporterName, Customers.SupportersRef, Customers.CustomerID
  from Transactions
  inner join Customers on Transactions.InternalRef = Customers.InternalRef
  where Transactions.SupportersRef = @Ref
  order by Transactions.TransactionDate DESC
END

I tried the following but did not work and gave the error subquery returned too many values:

IF @CustomerType = 'Customer'
BEGIN
select transactions.*, customers.*, IF (Transactions.SupportersRef IS NULL) BEGIN (select Customers.SchoolOrSupporterName from Customers inner join Customers on Transactions.InternalRef = Customers.InternalRef where Transactions.InternalRef = @Ref) END ELSE BEGIN (select c.SchoolOrSupporterName from Customers c inner join Customers on Transactions.SupportersRef = c.InternalRef where Transactions.InternalRef = @Ref) END AS CustomerOrSupporterName
..............
END
0
Comment
Question by:liaminnes
[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
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17107074
select coalesce(sc.name, ic.name) as Name, t.name
from transactions t
left join customers sc
  on sc.internalref = t.Supportersref
left join customers ic
  on ic.internalrf = t.InternalRef
0
 

Author Comment

by:liaminnes
ID: 17107855
Thanks angelIII - that did the trick! - never knew about the coalesce function.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

717 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