views:

51

answers:

2

I have two tables

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

and

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

+1  A: 
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.

Jimmeh
I want pickupPointName And PickUpPointId too:)
hrishi
I've added that in. You just add a selector for each of the fields you want. The join ensures that fields from both tables are available.
Jimmeh
I appreciate ur help buddy...but i want dropdownpoint name too...ie out put with two different name and two diffrent Idstnx
hrishi
I've editted it for you. It works, but i'm not sure it's good practice to use joins like that :)
Jimmeh
You might need left joins on both of those if you can't guarantee that the parent record will have both
HLGEM
A: 

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.

Ben Fransen
I appreciate ur help buddy...but i want dropdownpoint name too... ie out put with two different name and two diffrent Ids tnx
hrishi
While the tbl prefix is useless, the use of under_scores vs. CamelCase for readability is totally subjective. I prefer CamelCase in table names, but that is because I find them easier to read and type than underscores. YMMV.
Aaron Bertrand