Simple Select stmt : Subquery returned more than 1 value 2

[macsql1]

I have 2 tables

Division Table
DivisionId (Pk, uniqueidentifier,not null)
DivisionName (nvarchar)
DivisionLocation(nvarchar)

Region Table
RegionId (Pk, uniqueidentifier)
RegionName (nvarchar)
RegionManager(nvarchar)
RegiondivisionID (FK,uniqueidentifier,null)


Here i need the name of division table as 'Record of Divisionname' through RegiondivisionID column.

Select Regionname, RegionManager,
'Record of Divisionname' = (select d.DivisionName from Division d, Region r
where r.RegiondivisionID = d.DivisionId)
from Region

Getting this error:-
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Kinldy suggest.

-mac

 

[IanWaterman]

YOu could amend query with a where clause to reduce dataset to one record or you could just do a distinct

(select distinct d.DivisionName from Division d, Region r
where r.RegiondivisionID = d.DivisionId)

Ian

 

[RiverGuy]

Just what the error says. For each record in Region, you are getting zero to many matches in Division. If possible, you might join on more criteria to make the records unique.

If you standard joining logic, you will see this more clearly.

SELECT RegionName, RegionManager,
d.DivisionName
FROM Region r
LEFT OUTER JOIN Division d ON r.RegionDivisionId = d.DivisionID

 

[whosrdaddy]

what is wrong with a join?

SELECT r.Regionname, r.RegionManager, d.DivisionName AS 'Record of Divisionname' 
  FROM Region as r
  INNER JOIN Division ON r.RegiondivisionID = d.DivisionId
[/oode]

Cheers,
Daddy

-----------------------------------------------------
 What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

oops

too late

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[macsql1]

Hey whosrdaddy,

You are also a Star. why am not able to mark you as Star.