CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008joinsql-server-2000
sqlnewbie
sqlnewbie

Reputation: 867

How to implement joins inside sub-query referring tables in main query in SQL 2008

I have a following query which has joins in sub-queries and joined table is referred from the main query from clause. This is SQL Server 2000 syntax, I am trying to migrate it to 2008 syntax but I get an error at runtime. Please suggest.

CREATE TABLE [dbo].[PRODUCT]
(
        [pid] [int] NULL,
        [NAME] [nchar](10) NULL,
        [PDID] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[PRODUCTDESC]
(
        [PDID] [int] NULL,
        [DESC] [nchar](10) NULL
) ON [PRIMARY]

--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 PRODUCTDESC VALUES (1,'ONENEN');
INSERT INTO PRODUCTDESC VALUES (2,'TWEONEN');

-- SQL Server 2000
SELECT 
    Name,
    (SELECT [DESC]
     FROM PRODUCTDESC
     WHERE PRODUCT.PDID *= PRODUCTDESC.PDID)
FROM   
    PRODUCT 

--RESULTS
/*
    Name    (No column name)
    ONE         ONENEN    
    2           TWEONEN   
    3           TWEONEN   
    4           NULL
    */

-- SQL Server 2008
SELECT 
    NAME,
    (SELECT [DESC]
     FROM PRODUCT
     LEFT OUTER JOIN PRODUCTDESC ON PRODUCT.PDID = PRODUCTDESC.PDID)
FROM   
    PRODUCT 

--RESULTS
/*
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
*/

In the above 2008 query with in sub query how can we access the TABLES referred in main query form clause, so that I don't get sub query returend more than 1 value error

Please suggest.

Upvotes: 0

Views: 2002

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

You don't need the outer join in your sub query. This will work just fine.

SELECT Name,
       (SELECT [DESC]
        FROM   PRODUCTDESC
        WHERE  PRODUCT.PDID = PRODUCTDESC.PDID)
FROM   PRODUCT

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89711

SELECT PRODUCT.NAME
       ,PRODUCTDESC.[DESC]
FROM   PRODUCT
LEFT JOIN PRODUCTDESC
    ON PRODUCT.PDID = PRODUCTDESC.PDID

Note that the problem is because you have a scalar subquery, which MUST return only a single row (or no row). Note that this means that the original query with that data would fail in SQL Server 2000 as well. In addition, in both SQL Server 2000 and SQL Server 2008R2 and all version in between, the left join *= operator is not even necessary:

DECLARE @PRODUCT TABLE (
    [PDID] [int] NULL,
    [NAME] [nchar](10) NULL
)

DECLARE @PRODUCTDESC TABLE (
    [PDID] [int] NULL,
    [DESC] [nchar](10) NULL
)

--Test Data
insert into @PRODUCT values (1,'ONE');
insert into @PRODUCT values (2,'2');
insert into @PRODUCT values (3,'3');
insert into @PRODUCT values (4,'4');

INSERT INTO @PRODUCTDESC VALUES (1,'ONENEN');
INSERT INTO @PRODUCTDESC VALUES (2,'TWEONEN');


--SQL 2000-2008R2
SELECT Name,
       (SELECT [DESC]
        FROM   @PRODUCTDESC AS PRODUCTDESC
        WHERE  PRODUCT.PDID = PRODUCTDESC.PDID)
FROM   @PRODUCT AS PRODUCT

Upvotes: 2

Baz1nga
Baz1nga

Reputation: 15579

Is this what you want to do?

select p.Name,pdesc.DESC, from PRODUCT p inner join PRODUCTDESC pdesc on pdesc.PDID=p.PDID

Upvotes: 0

Related Questions