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');
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"
Why do you need a subquery anyway? You could use a left outer join in the first place?
Thats one approach but i would like to use the same table defined in OUTER query that is PRODUCT P
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.
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.
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.
I agree with you William, and it's more efficient than a "correlated subquery"
