SQL 2008

Hello,
can you please help,
I'm trying to use attached code, but I get error

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

note:
I tried to post the code here, but I keep getting a message from Expert exchange
All
 tags must have a closing 

Open in new window

tag.

Thanks,
Code.txt
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuinnDexCommented:
your sub queries could be returning more than 1 record, this will return 1 record but the data could be from different records depending on how they are ordered

you are getting the error posting code because of the [ c o d e] in your query, i have added ID to this in order to allow posting

Select top 1 TransactionNumber,AccountNumber,
(Select top 1 AccountCode from Clients where Clients.Accountnumber = AROpenTransactions.Accountnumber) as [CodeID],
(Select top 1 Name from Clients where Clients.Accountnumber = AROpenTransactions.Accountnumber) as [Name],
(Select top 1 Email from clientcontacts where clientcontacts.Accountnumber = AROpenTransactions.Accountnumber AND ClientContacts.NotifyOnInvoices= 1) as [Email], transactionDate 
from AROpenTransactions
where transactionDate = '2013-08-15'

Open in new window

0
W.E.BAuthor Commented:
thanks,
your code will return only one value.

I'm trying to get all values.
some Accountnumber will have more than one ClientContacts .

thanks again,
0
Anthony PerkinsCommented:
Something like this perhaps:
SELECT  t.TransactionNumber,
        t.AccountNumber,
        c.AccountCode Code,
        c.NAME,
        cc.Email,
        t.transactionDate
FROM    AROpenTransactions t
	INNER JOIN Clients c ON t.Accountnumber = c.Accountnumber		-- Change to an OUTER JOIN if neccesary
	INNER JOIN clientcontacts cc ON t.Accountnumber = cc.Accountnumber	-- Change to an OUTER JOIN if neccesary (don't forget to move the WHERE condition here)
WHERE   t.transactionDate = '2013-08-15' 
	AND cc.NotifyOnInvoices = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
thank you very much
0
PortletPaulfreelancerCommented:
I would suggest you look carefully at the notes acperkins has provided regarding outer joins

Whilst it is unlikely you have a transaction without a client, it may be possible for a client record to exist without an entry in clientcontacts (? i.e. we don't know if this true or not)

If you don't know this either, you might inspect by doing some counts, e.g.
select count(*)
FROM    AROpenTransactions t
LEFT OUTER JOIN Clients c ON t.Accountnumber = c.Accountnumber
where c.Accountnumber IS NULL

-- if this number is zero then an INNER JOIN is fine

select count(*)
FROM    AROpenTransactions t
LEFT OUTER JOIN Clients c ON t.Accountnumber = c.Accountnumber
LEFT OUTER JOIN clientcontacts cc ON t.Accountnumber = cc.Accountnumber
	AND cc.NotifyOnInvoices = 1  -- ( move the former WHERE condition here as join condition)
where cc.Accountnumber IS NULL

-- if this number is zero then an INNER JOIN is fine

Open in new window

if either number is > zero then use an outer join
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.