CODEWITHSUNDEEP
sqlsql-server-2008
punter
punter

Reputation: 460

Self join sql server

I have following table which stores records of merged projects . Here Merge1 is primary project and Merge2 is secondary project which means Merge2 gets merged into Merge1 .

Merge1  Merge2
------------------
1        2
3        1
4        3
4        8
9        6
7        9

Now i want to write a query which will return the contents of particular project say project id 4. here project 4 contains 8,3,1 &2 .

How can I do it in SQL?

Upvotes: 0

Views: 81

Answers (1)

Vitap Ramdevputra
Vitap Ramdevputra

Reputation: 160

Here is the query which gives required result. (Fiddle)

with cte_merge (projectid)
AS
(
  select merge2 from testBOM where merge1=4
  UNION  ALL
  select merge2 from testBOM inner join cte_merge on testBOM.merge1 = cte_merge.projectid
)
select * from cte_merge

Hope it helps you.

Upvotes: 1

Related Questions