• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

MySql Query

Hello,

Is there a more efficient way of doing below?

I need to display the name where the name has not completed a record in the ws_vp table for more than 2 days. Name only ever completes one record per date.

Thanks.
SELECT distinct(ws_vp.UID), tbl_usermanager.name from tbl_usermanager, ws_vp where ws_vp.UID not in (SELECT distinct(UID)FROM ws_vp where DATE = DATE_ADD( CURDATE( ) , INTERVAL -2 DAY ))and ws_vp.UID=tbl_usermanager.id order by DATE DESC

Open in new window

0
AUCKLANDIT
Asked:
AUCKLANDIT
3 Solutions
 
Chris HarteThaumaturgeCommented:
You are using date_add with a minus number, shouldn't that be date_sub with a 2.

DATE_SUB( CURDATE( ) , INTERVAL 2 DAY )
0
 
johanntagleCommented:
Try left outer join instead of not in (hard to type code using a tablet so that's as far as I can help for now)

@munterman - it achieves the same thing, both functions are acceptable
0
 
FugasCommented:
Distinct operation is slow, cause it is using also ordering. I think you shouldn't have both of those tables in the first select. I thinnk you have the id in both your tables, so try something like this.

SELECT tbl_usermanager.id, tbl_usermanager.name
from tbl_usermanager
where tbl_usermanager.id not in (SELECT UID FROM ws_vp where DATE = DATE_ADD( CURDATE( ) , INTERVAL -2 DAY ))

I don't know what result you really need, so if you like more help, post your table definitions and maybe more explain your idea. Thanks
0
 
Vimal DMCommented:
Hi,

I shall give you a logic for,

Having the "createdon" and  "updatedon" fields you can solve this issue very well.

So just write a query to minus the (updatedon-createdon) you will get the result based on the select the names
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now