Avatar of W.E.B
W.E.B asked on

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
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
SOLUTION
QuinnDex

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
W.E.B

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,
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
W.E.B

thank you very much
PortletPaul

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes