Brandin Arsenault Brandin Arsenault - 2 months ago 6
MySQL Question

MySQL Two Queries Producing One Value

I need to make a query that selects from two different tables. Basically, I only want to select the rows from the

dates
table that have no pending orders in the
orders
table.

For example, the
dates
table has the values of July 1, July 2 and July 3. July 2 has orders with the
status = PEN
in the
orders
table so the table will only show July 1 and July 3.

Query 1 for
dates
:

$sql = "SELECT * FROM dates WHERE DATEDIFF(CURDATE(), date) >= 30 AND `30day`='No'";


I have yet to build a query for the
orders
table as I am sure this needs to be integrated into one query together, and I am not sure what to do.

I know you can do two
SELECT
queries in one, and I am aware of how to do this, but I am unsure of how to cause the second
SELECT
query to be affected by the first
SELECT
query.

dates
database has columns
id
date
closed
30day
60day


orders
database has columns
id
date
order
status


I need this query to flag any orders with the statuses
PEN
BO
FBO
.

Thank you in advance!

Answer

could be this what you are looking for (i don't know your schema so for the join i have used a column named key

$sql = "SELECT * 
         FROM dates
         LEFT JOIN orders on (dates.date = orders.date  and orders.status not in ('PEN','BO', 'FBO'))
         WHERE DATEDIFF(CURDATE(), dates.date) >= 30 
         AND `30day`='No'";

otherwise if you need that status is not PEN, BO and FBO then you can

$sql = "SELECT * 
        FROM dates 
        LEFT JOIN orders ON  dates.date = orders.date  
        WHERE DATEDIFF(CURDATE(), date) >= 30 
        AND `30day`='No' 
        AND orders.status NOT IN ('PEN','BO', 'FBO'   ) ";