Need help with inner select "Subquery returned more than 1 value"

[Legioona]

Hello,

 

my query is as follows:

 

SELECT

 

businesskey as SAPID,

fulladdress as SAPaddress,

joinid,

 

 

(SELECT mmdb.dbo.shippingcompany.fulladdress FROM mmdb.dbo.shippingcompany WHERE mmdb.dbo.shippingcompany.shippingcompanyid =

(SELECT CASE WHEN mmdb.dbo."Join".Datasource1 = 3 THEN mmdb.dbo."Join".Join1

                WHEN mmdb.dbo."Join".Datasource2 = 3 THEN mmdb.dbo."Join".Join2

                WHEN mmdb.dbo."Join".Datasource3 = 3 THEN mmdb.dbo."Join".Join3 

                WHEN mmdb.dbo."Join".Datasource4 = 3 THEN mmdb.dbo."Join".Join4

            ELSE 0 END FROM mmdb.dbo."Join" WHERE mmdb.dbo."Join".JoinID = mmdb.dbo.shippingcompany.joinid)) AS MMDBFulladdress

 

 

FROM

 

mmdb.dbo.shippingcompany

 

WHERE

 

datasourceid = 5

and

rowstatusid = 1

and

joinid <> - 1

and

shippingcompanytypeid <> 4

 

This produces an error "Subquery returned more than 1 value".

 

On the other hand, if I comment out the line:

 

(SELECT mmdb.dbo.shippingcompany.fulladdress FROM mmdb.dbo.shippingcompany WHERE mmdb.dbo.shippingcompany.shippingcompanyid =

 

and remove the extra parenthesis before the AS , it works like a charm, returning

 

10026 Ortsteil:Motzen 48635 6096

10536 Shiwei Port 47978 2130

11340 Ul. Ludowa 13 39722 12442

.

.

.

 

Why oh why doesn't the original query work?

 

All help very much appreciated.