CODEWITHSUNDEEP
sqlsql-servert-sqlparent-child
bmsqldev
bmsqldev

Reputation: 2735

Issue with Parent-Child Relationship in Sql-Server

Suppose I have two tables with parent-child relationship in sql server as below,

parent table:

Parentid  value 

1         demo
2         demo2

child table: 

childid     parchildid   subvalue

1           1            demo1
2           1            demo2

here parchildid from child table is a foreign key referring parentid of the parent table.

I needed to retrieve child table data for a particular parentid. So, I used below query

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

It gave the below output. ( all the rows for child table)

 childid    parchildid   subvalue

  1         1            demo1
  2         1            demo2

But as you see, I have given a invalid column (parchildid) in the sub-query ( parchildid belongs to child table not the parent table ).

I wonder why sql server didn't throw any error.

running select parchildid from parent query alone thows invalid column error.

could anyone explains why there is no error thrown in the sub-query? hows the logic works there?

Thanks

Upvotes: 4

Views: 1122

Answers (5)

Sanjay
Sanjay

Reputation: 342

do like this 

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

Upvotes: 1

Praveen ND
Praveen ND

Reputation: 560

Since your requirement is to retrieve child table data for a particular parentid Use the parentid instead of parchildid. Here since parchildid from child table is a foreign key referring parentid of parent table, there is no issue. Else it affect your result set . Please check the below code 

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')

Using parchildid

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

Output :

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

Using Parentid

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

Output:

Childid parchildid  subvalue
 1          1           demo
 2          1           demo2

Upvotes: 1

Abhinav
Abhinav

Reputation: 2085

It is equivalent to writing:

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

If you notice, child has an alias of c which is accessible inside the subquery.

It is also like writing:

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

Upvotes: 2

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

It gives only those records which have parent and child id same.

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

From MSDN:

If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

In your case, since parchildid is a column from the table in the outer query, there is no error. On it's own however, the query cannot find such a column, and so it fails.

Upvotes: 4

Related Questions