liaminnes
asked on
sql select problem - subquery returned too many values
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,T ransaction s.Transact ionDate,Tr ansactions .Amount, Transactions.Description,C ustomers.S choolOrSup porterName , Customers.SupportersRef, Customers.CustomerID
from Transactions
inner join Customers on Transactions.InternalRef = Customers.InternalRef
where Transactions.SupportersRef = @Ref
order by Transactions.TransactionDa te 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.SupportersRe f IS NULL) BEGIN (select Customers.SchoolOrSupporte rName 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
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,T
from Transactions
inner join Customers on Transactions.InternalRef = Customers.InternalRef
where Transactions.SupportersRef
order by Transactions.TransactionDa
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.SupportersRe
..............
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER