MySql Query

Posted on 2011-10-11
Last Modified: 2013-12-12

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.

SELECT distinct(ws_vp.UID), 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 order by DATE DESC

Open in new window

Question by:AUCKLANDIT
    LVL 16

    Expert Comment

    by:Chris Harte
    You are using date_add with a minus number, shouldn't that be date_sub with a 2.

    LVL 24

    Assisted Solution

    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
    LVL 4

    Accepted Solution

    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.

    from tbl_usermanager
    where 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
    LVL 7

    Assisted Solution

    by:Vimal DM

    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

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now