select query in sql server 2008

I have two tables

create table tblchildinfo
  (id int, name varchar(50), 
   pickuppointid int, dropdownpointid int)

and

开发者_JS百科create table tblpoint(pointid int, PointName varchar(50))

So I have primary table tblpoint and child table as tblchildinfo and i want to write a statement such that i will get child id, child name, pickuppointID, pickuppointName, dropdownpointId & dropdownpoint name

Using SQL Server

select c.id, c.name, p.name, p.pointid, p2.pointid, p2.name
from tblchildinfo c
join tblpoint p on c.pickuppointid = p.pointid
join tblpoint p2 on c.dropdownpointid = p2.pointid
where <Insert where clause>

should do you.

Use a conditional query over two tables or use a LEFT JOIN on tblpoint. Btw, for readability i sugguest using unserscores for 'multi-word-name' tables, such as tbl_child_info and tbl_point.