Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below.

select datediff((select curdate()),
(select Company.CreatedOn
from
dbname.company
inner join
dbname.user 
on
user.company_id = company.company_id
))

I am getting the result as follows,

Error Code: 1242 Subquery returns more than 1 row

I do understand the error, but I can't use any id's to point out as follows,

select datediff
(
(select curdate()),
(select Company.CreatedOn
from
dbname.company
inner join
dbname.user
on
user.company_id = company.company_id
where
company.company_id = 17)
);

While I run this query I got the output as follows,

38

I need to select the users depends on the company which registered time exist more than 365 days.

Need to display all the company which registered time more than 365 days, I have not mentioned this condition in code,

I need to solve Subquery result, datediff function should compare with all the company CreatedOn value and results only the companies which exists more than 365 days.

Any help would be grateful.

share|improve this question
    
use the limit command to get only one result –  Up_One Mar 13 at 15:35
add comment

2 Answers 2

up vote 4 down vote accepted

Aaron's answer is essentially right, but I would not recommend DATEDIFF in the WHERE clause because it could possibly impose a table scan. Instead, I suggest this

SELECT
   user.*,
   company.*.
   DATEDIFF(Company.CreatedOn,NOW()) NumDays
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
WHERE company.CreatedOn < DATE(NOW() - INTERVAL 1 YEAR);

You could take further and refactor the query as follows:

SELECT
   U.*,
   C.*.
   DATEDIFF(C.CreatedOn,NOW()) NumDays
from
(select * from dbname.company
WHERE CreatedOn < DATE(NOW() - INTERVAL 1 YEAR)) C
inner join dbname.user U
on U.company_id = C.company_id;
share|improve this answer
    
Isn't the where clause in the wrong place in the second query? –  Aaron Bertrand Mar 13 at 18:21
    
@AaronBertrand no, the parenthesis are kind of confusing, C is a derived table. –  ypercube Mar 13 at 18:23
    
@ypercube ah I see, bad indentation fooled me again –  Aaron Bertrand Mar 13 at 18:26
    
I understood, thanks. –  rathishDBA Mar 17 at 5:26
add comment

Uh, why not just

select /* user.columns, */
       /* company.columns, */
    NumDays = DATEDIFF(Company.CreatedOn, curdate())
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
where Company.CreatedOn < curdate() - INTERVAL 365 DAY;
share|improve this answer
1  
I'd make that where Company.CreatedOn < curdate() - INTERVAL 365 DAYS ; Reference to the blog of a clever chap –  ypercube Mar 13 at 18:01
    
@Ypercube changed, thanks, but to be fair I have very little clue about how these things work in MySQL. :-) Was just trying to help with the logic. –  Aaron Bertrand Mar 13 at 18:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.