asked on 9/1/2013

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

Last Comment

PortletPaul

9/2/2013

SOLUTION

QuinnDex

9/1/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

W.E.B

9/1/2013

ASKER

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

9/1/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

W.E.B

9/1/2013

ASKER

thank you very much

PortletPaul

9/2/2013

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