Phrasing SQL that has subquery returning multiple rows

Question

I am tring to query a set of data from mySQL with the following SQL:

SELECT * FROM orders 
WHERE datediff((now()), (SELECT datepurchased FROM orders WHERE email = '[email protected]')) <= 1

But the problem is I get the error that #1242 - Subquery returns more than 1 row because the statement SELECT datepurchased FROM orders WHERE email = '[email protected]' is returning more than one rows, which is perfectly correct.

The idea that I wanted is to select from the orders table such that the date difference between the current time and the datepurchased field is <=1 and that the email field is equals to [email protected].

There could be many rows in the orders table with their email fields as [email protected]. I just want to find out all of these rows with email fields equals [email protected] that have datepurchased less than or equals one.

How should I phrase my SQL to achieve this?

Someone please tell me why Don Kirkby's answer is being down voted when it is the exact same as the other 4 current answers and they are being voted up? — Bryan
– Bryan, Commented Dec 16, 2011 at 20:58
@Joe Stefanelli - Probably but just wanted to make sure I wasn't missing something. I dislike the whole down vote with no reason thing, it turns a chance for constructive criticism into near-uselessness. Completely counter productive for a site that is supposed to be about learning and problem solving..... not necessarily in that order. — Bryan
– Bryan, Commented Dec 16, 2011 at 21:05

Joe Stefanelli · Accepted Answer · 2011-12-16 20:54:43Z

5

There's no need for a subquery in this case. Simply:

SELECT *
    FROM orders
    WHERE email = '[email protected]'
        AND DATEDIFF(NOW(), datepurchased) <= 1;

answered Dec 16, 2011 at 20:54

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Don Kirkby · Accepted Answer · 2011-12-16 20:54:32Z

2

You don't need a subquery, just put two where conditions on your orders query.

SELECT * 
FROM   orders 
WHERE  datediff(now(), datepurchased) <= 1
AND    email = '[email protected]'

I haven't tried it, but it seems simple enough.

answered Dec 16, 2011 at 20:54

Don Kirkby

56.7k28 gold badges225 silver badges307 bronze badges

Comments

Mark Byers · Accepted Answer · 2011-12-16 20:54:34Z

2

I think you just want an ordinary SELECT with a WHERE clause containing two conditions:

SELECT * FROM orders 
WHERE email = '[email protected]')
AND datediff(now(), datepurchased) <= 1

answered Dec 16, 2011 at 20:54

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Comments

Brian Hoover · Accepted Answer · 2011-12-16 20:54:44Z

2

I think I'm missing something, but why wouldn't you be able to do this?

SELECT * FROM orders 
WHERE datediff(now(), datepurchased ) <= 1
and email = '[email protected]'

answered Dec 16, 2011 at 20:54

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Comments

SarekOfVulcan · Accepted Answer · 2011-12-16 20:56:01Z

0

What's wrong with

SELECT * 
    FROM orders
    WHERE datediff(now(), datepurchased) <= 1
      AND email = '[email protected]'

?

answered Dec 16, 2011 at 20:56

SarekOfVulcan

1,3586 gold badges16 silver badges36 bronze badges

Collectives™ on Stack Overflow

Phrasing SQL that has subquery returning multiple rows

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related