CODEWITHSUNDEEP
mysqlsqlsql-serverdatetime
Myself
Myself

Reputation: 13

Un Ordered Customer

I have two tables fist one customer and second one Orderdetails. I want list of sign ups from 4th on wards and exclude the list of customers who have bought.

    SELECT *
FROM Customer
WHERE RegisteredDate BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/04/2015',101)) 
                         AND CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/07/2015',101)) 
 AND CustomerID NOT IN(SELECT *
                       FROM Customer c
                       INNER JOIN orderdetails od ON c.CustomerId = od.CustomerID
                       WHERE C.RegisteredDate 
                     BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/04/2015',101)) 
                         AND CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/07/2015',101)) 
                         AND Transactionid IS NOT NULL
                       )

the result is:

Msg 116, Level 16, State 1, Line 13 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 1

Views: 53

Answers (3)

M.Ali
M.Ali

Reputation: 69554

As mentioned in other answer IN operator only expects one column to be returned when being used with a sub-query, you should only select the cutomerID in the sub-query but if there is a chance of it returning NULL values then use EXISTS operator something like....

SELECT *
FROM Customer c1
WHERE RegisteredDate BETWEEN CONVERT(DATETIME,'12/04/2015',101) 
                         AND CONVERT(DATETIME,'12/07/2015',101) 
AND NOT EXISTS (SELECT *
               FROM Customer c
               INNER JOIN orderdetails od ON c.CustomerId = od.CustomerID
               WHERE C.RegisteredDate 
             BETWEEN CONVERT(DATETIME,'12/04/2015',101) 
                 AND CONVERT(DATETIME,'12/07/2015',101) 
                 AND Transactionid IS NOT NULL
                 AND C1.CustomerID = c.CustomerID 
               )

Upvotes: 2

Simon
Simon

Reputation: 1333

You cant use select * in a subquery, you need to specify the column which in this case is customerid.

SELECT *
FROM Customer
WHERE RegisteredDate BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/04/2015',101)) 
                     AND CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/07/2015',101)) 
AND CustomerID NOT IN(SELECT c.CustomerID
                   FROM Customer c
                   INNER JOIN orderdetails od ON c.CustomerId = od.CustomerID
                   WHERE C.RegisteredDate 
                 BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/04/2015',101)) 
                     AND CONVERT(DATETIME,CONVERT(VARCHAR(50),'12/07/2015',101)) 
                     AND Transactionid IS NOT NULL
                   )

Upvotes: 1

Aj.na
Aj.na

Reputation: 283

Remove * (all columns) in subquery and specify select CustomerID instead of *(all columns) in subquery.

Because when using NOT IN, it is expected that the number of columns return by the subquery is only one.

Upvotes: 2

Related Questions