Geek Answers Handbook

Problem with parent-child relationships in Sql-Server

Suppose I have two tableswith a parent-child relationship in sql serveras shown below

parent table:

Parentid  value 

1         demo
2         demo2

child table:

childid     parchildid   subvalue

1           1            demo1
2           1            demo2

here parchildidfrom child tableis a foreign keylink parentid parent table.

I needed to get child table data for a specific parent. So, I used below request

 select *from child
 where parchildid in (select parchildid from parent)

He gave the result below. (all lines for child table)

 childid    parchildid   subvalue

  1         1            demo1
  2         1            demo2

But as you can see, I gave the column invalid( parchildid) in the subquery ( does not parchildidbelong ).child tableparent table

I wonder why I sql serverdidn’t give any error.

running select parchildid from parent query only thows invalidcolumn error.

- , ? ?

+4
5

:

select *
  from child c
 where c.parchildid in 
     (
        select c.parchildid
          from parent p
     )

, child c, .

:

select *
  from child c
 where Exists 
     (
        select *
          from parent p
         where c.parchildid = c.parchildid
     )
+2

MSDN:

, FROM , , FROM .

, parchildid , . , .

+4

, .

+1

, parentid parchildid. , parchildid , parentid , . . , .

DECLARE @Parent TABLE
(Parentid int, Value varchar(10))

DECLARE @Child TABLE
(Childid int, parchildid int, subvalue varchar(10))

INSERT @Parent
(Parentid,[Value])
VALUES
(1,'demo'),(2,'demo2')

INSERT @Child
(Childid,parchildid,subvalue)
VALUES
(1,1,'demo'),(2,1,'demo2'),(3,3,'demo3')

parchildid

select * from @Child
where parchildid in (select parchildid from @Parent)

:

 Childid    parchildid  subvalue
 1          1           demo
 2          1           demo2
 3          3           demo3

Parentid

select * from @Child
where parchildid in (select Parentid from @Parent)

:

Childid parchildid  subvalue
 1          1           demo
 2          1           demo2
+1

 select * from child
 where parchildid in (select Parentid from parent)
+1

More articles:


All Articles