Using JOIN instead of subquery

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)

Using JOIN instead of subquery

Author

Topic

robbase9
Starting Member

5 Posts

Posted - 2009-08-13 : 14:00:16

I have a view that is currently using a subquery with a TOP 1 clause in it. Is it possible to get rid of the subquery and use a JOIN instead?

SELECT
P.PERSONPK AS PERSONPK
, P.REALNAME AS PERSONNAME
, P.MINHEIGHT AS MINHEIGHT
, P.MAXHEIGHT AS MAXHEIGHT
, CAST(P.MINWEIGHT AS NUMERIC(4,0)) AS MINWEIGHT
, CAST(P.MAXWEIGHT AS NUMERIC(4,0)) AS MAXWEIGHT
, P.GENDER AS GENDER
, P.RACE AS RACE
, P.EYECOLOR AS EYECOLOR
, P.HAIRCOLOR AS HAIRCOLOR
, P.DOB AS DOB
, P.MINDOB AS MINDOB
, P.MAXDOB AS MAXDOB
, (SELECT NAMEPRE
FROM PERSONNAMES
WHERE NAMEPK =(SELECT TOP 1 NAMEPK
FROM PERSONDESC PD
WHERE P.PERSONPK = PD.PERSONPK
ORDER BY DESCPK DESC)
) AS NAMEPRE
FROM [dbo].[PERSONS] P

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-13 : 14:42:23

try


SELECT
P.PERSONPK AS PERSONPK
, P.REALNAME AS PERSONNAME
, P.MINHEIGHT AS MINHEIGHT
, P.MAXHEIGHT AS MAXHEIGHT
, CAST(P.MINWEIGHT AS NUMERIC(4,0)) AS MINWEIGHT
, CAST(P.MAXWEIGHT AS NUMERIC(4,0)) AS MAXWEIGHT
, P.GENDER AS GENDER
, P.RACE AS RACE
, P.EYECOLOR AS EYECOLOR
, P.HAIRCOLOR AS HAIRCOLOR
, P.DOB AS DOB
, P.MINDOB AS MINDOB
, P.MAXDOB AS MAXDOB
,c.NamePre
From
Persons p
	inner Join
(
	SELECT	  row_Number() over (Partition By pd.PersonPK order by pd.descpk desc) as RowID
			, pd.NAMEPK
			,Pd.PersonPK 
	FROM 
		PERSONDESC PD
) b
on p.pDPersonPK = b.PDPersonPK
Inner Join
PERSONNAMES  c
on b.NamePK = c.NamePK
where b.RowID = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robbase9
Starting Member

5 Posts

Posted - 2009-08-13 : 14:47:21

thanks for the response.

I get an error when running your suggestion:

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'Partition'.

robbase9
Starting Member

5 Posts

Posted - 2009-08-13 : 14:50:39

Figured out the syntax error, but I'm now returning a different number of rows between your query and my original query.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-13 : 14:50:59

recopy now, I corrected the error.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robbase9
Starting Member

5 Posts

Posted - 2009-08-13 : 14:59:39

Worked that time. I'm getting a different number of rows because some records do not exist in the PERSONDESC table. Is there a way to include those missing rows too?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-13 : 15:07:49

try changing the inner joins to left joins

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-13 : 15:46:21

[code]
SELECT
P.PERSONPK AS PERSONPK
, P.REALNAME AS PERSONNAME
, P.MINHEIGHT AS MINHEIGHT
, P.MAXHEIGHT AS MAXHEIGHT
, CAST(P.MINWEIGHT AS NUMERIC(4,0)) AS MINWEIGHT
, CAST(P.MAXWEIGHT AS NUMERIC(4,0)) AS MAXWEIGHT
, P.GENDER AS GENDER
, P.RACE AS RACE
, P.EYECOLOR AS EYECOLOR
, P.HAIRCOLOR AS HAIRCOLOR
, P.DOB AS DOB
, P.MINDOB AS MINDOB
, P.MAXDOB AS MAXDOB
,c.NamePre
From
Persons p
Left Join
(
SELECT row_Number() over (Partition By pd.PersonPK order by pd.descpk desc) as RowID
, pd.NAMEPK
,Pd.PersonPK
FROM
PERSONDESC PD
) b
on p.pDPersonPK = b.PDPersonPK
Left Join
PERSONNAMES c
on b.NamePK = c.NamePK
where coalesce(b.RowID,1) = 1
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

robbase9
Starting Member

5 Posts

Posted - 2009-08-13 : 17:52:23

Worked like a champ. You're the bomb. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources