question

pvsrinivasrao avatar image
pvsrinivasrao asked

Using outer query tables in sub-query joins (defined in select clause)

Hi All, I have the below scenario, please suggest. Problem: I need to use outer table in sub-query join defined in select clause. (migrating 2000 to 2008) 2000- query SELECT Name, (SELECT [DESC] FROM PRODUCTDESC WHERE PRODUCT.PDID *= PRODUCTDESC.PDID) FROM PRODUCT 2008- query SELECT NAME, (SELECT [DESC] FROM P LEFT OUTER JOIN PRODUCTDESC ON PRODUCT.PDID = PRODUCTDESC.PDID) FROM PRODUCT P Here i am unable to use Use PRODUCT P table defined in OUTER query FROM caluse in SELECT Clause SUB Query....IS THERE ANY WAY I CAN do it and migrate my 2000 query properly. --Test DATA CREATE TABLE [dbo].[PRODUCT]( [pid] [INT] NULL, [NAME] [NCHAR](10) NULL, [PDID] [INT] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[PRODUCTDESC]( [PDID] [INT] NULL, [DESC] [NCHAR](10) NULL ) ON [PRIMARY] GO --Test Data INSERT INTO PRODUCT VALUES(1,'ONE',1); INSERT INTO PRODUCT VALUES(2,'2',2); INSERT INTO PRODUCT VALUES(3,'3',2); INSERT INTO PRODUCT VALUES(4,'4',NULL); INSERT INTO PRODUCT VALUES(5,'4',5); INSERT INTO PRODUCT VALUES(1,'ONENEN'); INSERT INTO PRODUCT VALUES(2,'TWEONEN');
sql-server-2008sqlsql-server-2000joinscorrelated
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
This should work, but it will give you nulls for all the products that doesn't have any description in Productdesc: SELECT NAME, (SELECT [DESC] FROM PRODUCTDESC WHERE P.PDID = PRODUCTDESC.PDID) FROM PRODUCT P If it doesn't work try this: SELECT NAME, (SELECT [DESC] FROM Product P2 LEFT OUTER JOIN PRODUCTDESC ON PRODUCT.PDID = PRODUCTDESC.PDID WHERE P2.PDID = P.PDID ) FROM PRODUCT P Third option is to use a left join in the FROM clause to remove the heavy overhead of having a correlated subquery in the SELECT clause (as WilliamD suggested). The fourth and maybe the best approach with complicated queries is to use CTE or temp tables, like this: ;WITH cteDesc AS ( -- do you "complex" logic here, but remember KISS ("keep it simple stupid") is preffered -- SELECT [DESC] FROM PRODUCTDESC WHERE P.PDID = PRODUCTDESC.PDID ) SELECT P.Name, D.Desc FROM Product P LEFT OUTER JOIN cteDesc AS D ON P.PDID = d.PDID "How do you eat an elephant? In small pices. How do you create a stored procedure? In small pices"
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
Why do you need a subquery anyway? You could use a left outer join in the first place?
1 Like 1 ·
pvsrinivasrao avatar image pvsrinivasrao commented ·
Thats one approach but i would like to use the same table defined in OUTER query that is PRODUCT P
0 Likes 0 ·
pvsrinivasrao avatar image pvsrinivasrao commented ·
Yes i could use it...but inorder to make the sample simpler i used this example...but the query is too complicated which i haven't used here..I am looking for a genric approach for such kind of issues...please suggest.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
You can simplify the join to a standard LEFT JOIN: SELECT P.NAME, PD.[DESC] FROM PRODUCT P LEFT JOIN PRODUCTDESC PD ON P.PDID = PD.PDID That is the easiest way of getting what you want, IMO.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
That is a generic solution. You change the sub-query into a standard left join.
2 Likes 2 ·
pvsrinivasrao avatar image pvsrinivasrao commented ·
Yes i could use it...but inorder to make the sample simpler i used this example...but the query is too complicated which i haven't used here..I am looking for a genric approach for such kind of issues...please suggest.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
I agree with you William, and it's more efficient than a "correlated subquery"
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.