Joining Parent and Child Tables

Question

Post reply

Joining Parent and Child Tables

aprabahar 76630

Newbie

Points: 6
More actions
September 1, 2012 at 3:42 am

#261322

Hi,
I have two following Tables.
Create table Employeeparent
(
Id Int Identity(1000,1),
Name NVarchar(100)
)
Create Table EmployeeChild
(
Id Int Not Null,
Name NVarchar(100)
)
Insert into Employeeparent Values('aa'),
('bb'),
('cc')
Select * From Employeeparent
IdName
1000aa
1001bb
1002cc
Insert into EmployeeChild Values(10001, 'abc'),
(10002,'bcd'),
(10011,'cde'),
(10012,'def')
Select * from EmployeeChild
IdName
10001abc
10002bcd
10011cde
10012def
EmployeeParent - contains Parent details id
EmployeeChild - Contains Child Details id, example, Employeeparent Id = 1000(is parent), EmployeeChild-Id = 10001,10002(child values like finally added 1,2...)
How to join both the tables with ID and finding the result. ParentName,childName
Please help me on this.
Thanks &Regards,
tony

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

aprabahar 76630 Newbie Points: 6 More actions · Answer 1

Hi,

Finally I got the Answer,

Select * from Employeeparent ep Join EmployeeChild ec On ep.Id = SUBSTRING(convert(Varchar(10),ec.id),1,Len(ep.id))

Thanks,

tony

Jeff Moden SSC Guru Points: 1004718 More actions · Answer 2

aprabahar 76630 (9/1/2012)
Hi,
Finally I got the Answer,
Select * from Employeeparent ep Join EmployeeChild ec On ep.Id = SUBSTRING(convert(Varchar(10),ec.id),1,Len(ep.id))
Thanks,
tony

Converting INTs to string datatypes is a fairly expensive operation. Consider using a bit of Integer math instead like the following...

SELECT *

FROM dbo.EmployeeParent p

INNER JOIN dbo.EmployeeChild c

ON p.ID = c.ID/10

The other thing is that the ID for the child table is improperly designed. It violates all normal forms because it contains two pieces of information. It contains the parent ID and a sequence number and you'll always end up with either a table scan or an index scan because you always end up with a non-SARGable criteria. Of course, the "divide by 10" won't work if you have more than 10 children.

Consider breaking the child ID into two columns instead of just the one. That will prevent the future world-of-pain the current design will cause you because you'll eventually end up with dupes. For example, you could have two numbers like 100013. That could be a parent ID of 10001 with a sequence number of 3 or it could be a parent ID of 1000 with a sequence number of 13.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)