SELECT Query for fetching data

Concept: The concept of the job is to send invoices every day but once the email is sent on that day, it should not sent the same email for the same user on the same day. But it should send the email to the same user the next day.

As it happens, I wrote a query using similar logic to what is necessary for this today..

You'll want to run something like this:

SELECT id FROM users
 WHERE NOT EXISTS (
    SELECT * FROM invoices
     WHERE invoice_date = ?
 )

Where that placeholder is the current date.. you may need to do some date casting or include a > and < range to capture a full day if the field is actually a datetime not just a date, but the where not exists is what you want to determine what from the full list of users does not exist in the invoices table.

@willvincent

DROP PROCEDURE IF EXISTS `get_users_halls`;
delimiter ;;
CREATE PROCEDURE `get_users_halls` ()
BEGIN
SELECT
    u.id , u.email,
    u.first_name,
    u.last_name,
    count(*) as total_halls,
    u.override_membership_price,
    CASE
        WHEN u.override_membership_price IS NULL THEN (((count(*)-1) * 50)+100)*0.1
            ELSE  u.override_membership_price * 0.1
    END AS gst_amount,
    CASE
        WHEN u.override_membership_price IS NULL THEN ((count(*)-1) * 50)+100
            ELSE u.override_membership_price
    END AS total_amount,
    u.subscription,
    u.expires_on,
     CASE
        WHEN u.override_membership_price IS NULL THEN TRUE
            ELSE FALSE
        END AS display_info
    FROM users u
    INNER JOIN halls h ON u.id=h.user_id
WHERE u.expires_on=DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)
AND u.activated=1 AND u.subscription='yearly' group by u.id;
END;
-- CALL `get_users_halls`;

this selects the users but I want to select the data that does not exist in the invoices table. Could you please suggest me how can I write the query in this situation.

Right, as I said, it will select the users not in the invoices table.. as you asked.

@willvincent I am also doing

SELECT id FROM users 
WHERE NOT EXISTS (SELECT user_id FROM invoices WHERE invoice_date=current_date());

but it is not fetching any data. Data type of invoice_date is date.

maybe there aren't any users not in the invoices table for the current date.. in that case no results would be the expected outcome

@willvincent There are many other users in users table, Just 5 users are in invoices table. Basically it should select all other users excepts the one in invoice table.

Try this, works at my end..

$users = \DB::table("users")->select('*')->whereNotIn('id',function($query) {
    $query->select('id')->from('invoices');
})->get();

@sauravs012 used the concepts to write the stored procedure. Thanks @sauravs012

In the future you might want to be more specific and clear...

I have to write a query/stored procedure that fetches only the data from the users table that doesn't exist in the invoices table on that day.

My original answer did that. Fetch data from user table, not in the invoice table, on a specific date.