Relationship Assign Parent Name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Relationship Assign Parent Name

Author

Topic

fawadafr
Starting Member

47 Posts

Posted - 2010-02-02 : 17:38:15

Hello,
I would like to assign "parent name" to all relationships we have in our "Relations" table. Here is the structure of our "Relations" table design:

I have writen the following SQL code so far:

SELECT r.Counter
      ,r.LineNum
      ,r.ParentNode
      ,r.Code
      ,(SELECT p.Name FROM Relations AS p WHERE r.LineNum = p.ParentNode AND p.ParentNode = p.LineNum) AS ParentName
      ,r.Name
      ,r.RelationType
      ,r.Address
      ,r.Phone
      ,r.Email
      ,r.Type
      ,r.UserCreated
      ,r.DateCreated
      ,r.UserModified
      ,r.DateModified
  FROM Relations AS r
  WHERE r.ParentNode <> r.LineNum
  ORDER BY r.ParentNode, r.RelationType, r.UserCreated, r.UserModified
GO

I get this error message when I execute my script above:

quote:
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.

Here is a screenshot of sample data in the relations table:

Could you please help?
Thank you,

--
Fawad Rashidi

mymatrix
Starting Member

24 Posts

Posted - 2010-02-03 : 00:01:37

You have made a small mistake in subquery due to which it returned multiple records.
p.LineNum = r.ParentNode instead of r.LineNum = p.ParentNode
to get the single row corresponding to single line number.

Please see the correct query below:

SELECT r.Counter
,r.LineNum
,r.ParentNode
,r.Code
,(SELECT p.Name FROM Relations AS p WHERE p.LineNum = r.ParentNode AND p.ParentNode = p.LineNum) AS ParentName
,r.Name
,r.RelationType
,r.Address
,r.Phone
,r.Email
,r.Type
,r.UserCreated
,r.DateCreated
,r.UserModified
,r.DateModified
FROM Relations AS r
WHERE r.ParentNode <> r.LineNum
ORDER BY r.ParentNode, r.RelationType, r.UserCreated, r.UserModified
GO

**************************************

Even my blood group says be -ve to all the negatives.

fawadafr
Starting Member

47 Posts

Posted - 2010-02-03 : 12:04:53

Thank you very much mymatrix. I believe it is working fine now.

--
Fawad Rashidi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources