SQL Server Performance Forum – Threads Archive

Join

create table ##TBL_Main
(
row_id int,
Parent_Id nvarchar (10)
) create table ##TBL_Child
(
row_id int,
Parent_Id nvarchar(10),
Child_id nvarchar(10)
)
insert into ##TBL_Main values(1,’111′) insert into ##TBL_Child values(1,’111′,’C1′)
insert into ##TBL_Child values(1,’111′,’C2′)
select m.row_id,m.parent_id,c.child_id
from ##TBL_Main m
join ##TBL_Child c on m.row_id = c.row_id
Output
——- Row_Id Parent_Id Child_Id
————————-
1 111 C1
1 111 C2 My requirement is to get only any 1 record from the ##TBL_Child. Desired Output
————–
Row_Id Parent_Id Child_Id
————————-
1 111 C1 How do i do that?

---

You have to use two embedded queries as a derived table, that gives you the row_id for the "first" ChildID value, like so: SELECT M.row_id, M.parent_id, C.child_id
FROM ##TBL_Main M
INNER JOIN
(SELECT X.Parent_Id, X.row_id FROM ##TBL_Child X
WHERE X.Child_Id IN (SELECT MIN(T.ChildId) Child_Id FROM ##TBL_Child T WHERE T.Parent_Id = X.Parent_Id)) C
ON M.row_id = C.row_id Perhaps you have to add AND M.Parent_Id = C.Parent_Id to the end of the query? Not sure how it all relates, and "row_id" seems like an odd name for an FK between parent and child tables.

---

You can even consider this: select m.row_id,m.parent_id,c.child_id
from #TBL_Main m
join #TBL_Child c on m.row_id = c.row_id
WHERE c.child_id in (SELECT TOP 1 child_id from #TBL_Child t WHERE t.row_id = c.row_id)

---

]]>