SQL to return self join record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL to return self join record

Author

Topic

johnnybutler7
Starting Member

16 Posts

Posted - 2008-06-25 : 10:57:10

I have a companies table:

id name primary_company_id status
---------------------------------
1 comp1 1
2 comp2 1 2
3 comp3 1 2
4 comp4 1
5 comp5 4 1

There are other fields in the table and basically i need to write a query which will return all the companies that match a criteria but in the case where a primary_company_id exisit then return the primary company instead of the company on that row

So if i wanted all the companies with a status of 2 from the criteria above i would get only comp1 back because the records 2 and 3 have a primary_company_id of 1:

id name primary_company_id status
---------------------------------
1 comp1 1

Can anyone help or point me in the right direction on this?

JB

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 11:06:10

select distinct t1.* from companies t1 inner join companies t2 on t1.id=t2.primary_company_id
where t2.status=2

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 11:09:03

[code]DECLARE @Sample TABLE (ID INT, Name VARCHAR(10), PrimaryID INT, [Status] INT)

INSERT @Sample
SELECT 1, 'comp1', NULL, 2 UNION ALL
SELECT 2, 'comp2', 1, 1 UNION ALL
SELECT 3, 'comp3', 5, 1 UNION ALL
SELECT 4, 'comp4', NULL, 2 UNION ALL
SELECT 5, 'comp5', NULL, 1

SELECT p1.*
FROM @Sample AS p1
LEFT JOIN @Sample AS p2 ON p2.ID = p1.PrimaryID
WHERE COALESCE(p2.[Status], p1.[Status]) = 2[/code]

E 12°55'05.25"
N 56°04'39.16"

johnnybutler7
Starting Member

16 Posts

Posted - 2008-06-25 : 11:27:43

quote:

Originally posted by Peso

DECLARE	@Sample TABLE (ID INT, Name VARCHAR(10), PrimaryID INT, [Status] INT)

INSERT	@Sample
SELECT	1, 'comp1', NULL, 2 UNION ALL
SELECT	2, 'comp2',    1, 1 UNION ALL
SELECT	3, 'comp3',    5, 1 UNION ALL
SELECT	4, 'comp4', NULL, 2 UNION ALL
SELECT	5, 'comp5', NULL, 1

SELECT		p1.*
FROM		@Sample AS p1
LEFT JOIN	@Sample AS p2 ON p2.ID = p1.PrimaryID
WHERE		COALESCE(p2.[Status], p1.[Status]) = 2

E 12°55'05.25"
N 56°04'39.16"

Ive tried the above sql and it returns all the companies with a status of 2 and not the primary company the are pointing to which is company 1.

SELECT p1.*
FROM companies AS p1
LEFT JOIN companies AS p2 ON p2.id = p1.primary_company_id
WHERE COALESCE(p2.[status], p1.[status]) = 2

??????

johnnybutler7
Starting Member

16 Posts

Posted - 2008-06-25 : 11:30:21

quote:
Originally posted by madhivanan

select distinct t1.* from companies t1 inner join companies t2 on t1.id=t2.primary_company_id
where t2.status=2

Madhivanan

Failing to plan is Planning to fail

This doesnt work either. It just returns a null data set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 13:03:40

[code]SELECT COALESCE(tmp.id,t.id) as id,
COALESCE(tmp.name,t.name) AS name,
COALESCE(tmp.primary_company_id,t.primary_company_id) AS primary_company_id,
CASE WHEN tmp.id IS NOT NULL THEN tmp.status ELSE t.status END as status
FROM YourTable t
OUTER APPLY(SELECT id, name, primary_company_id, status
FROM YourTable
WHERE id=t.primary_company_id
)tmp
WHERE t.status=2[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-25 : 13:30:46

[code]DECLARE @Sample TABLE (ID INT, Name VARCHAR(20), PrimaryID INT, [Status] INT)

INSERT @Sample
SELECT 1, 'comp1', NULL, 1 UNION ALL
SELECT 2, 'comp2', 1, 2 UNION ALL
SELECT 3, 'comp3', 1, 2 UNION ALL
SELECT 4, 'comp4', NULL, 1 UNION ALL
SELECT 5, 'comp5', 4, 1

;WITH Yak (OriginalID, [NewID], PrimaryID, Sequence)
AS (
SELECT ID,
ID,
PrimaryID,
0
FROM @Sample
WHERE [Status] = 2

UNION ALL

SELECT y.OriginalID,
s.ID,
s.PrimaryID,
y.Sequence + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.PrimaryID = s.ID
)

SELECT DISTINCT s.ID,
s.Name,
s.PrimaryID,
s.[Status]
FROM (
SELECT OriginalID,
[NewID],
ROW_NUMBER() OVER (PARTITION BY OriginalID ORDER BY Sequence DESC) AS RecID
FROM Yak
) AS d
INNER JOIN @Sample AS s ON s.ID = d.[NewID]
WHERE d.RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

johnnybutler7
Starting Member

16 Posts

Posted - 2008-06-26 : 04:20:32

quote:

Originally posted by Peso

DECLARE	@Sample TABLE (ID INT, Name VARCHAR(20), PrimaryID INT, [Status] INT)

INSERT	@Sample
SELECT	1, 'comp1', NULL, 1 UNION ALL
SELECT	2, 'comp2',    1, 2 UNION ALL
SELECT	3, 'comp3',    1, 2 UNION ALL
SELECT	4, 'comp4', NULL, 1 UNION ALL
SELECT	5, 'comp5',    4, 1

;WITH Yak (OriginalID, [NewID], PrimaryID, Sequence)
AS (
	SELECT	ID,
		ID,
		PrimaryID,
		0
	FROM	@Sample
	WHERE	[Status] = 2

	UNION ALL

	SELECT		y.OriginalID,
			s.ID,
			s.PrimaryID,
			y.Sequence + 1
	FROM		@Sample AS s
	INNER JOIN	Yak AS y ON y.PrimaryID = s.ID
)

SELECT DISTINCT	s.ID,
		s.Name,
		s.PrimaryID,
		s.[Status]
FROM		(
			SELECT	OriginalID,
				[NewID],
				ROW_NUMBER() OVER (PARTITION BY OriginalID ORDER BY Sequence DESC) AS RecID
			FROM	Yak
		) AS d
INNER JOIN	@Sample AS s ON s.ID = d.[NewID]
WHERE		d.RecID = 1

E 12°55'05.25"
N 56°04'39.16"

Thanks guys, this works fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources