Frank Mooij Frank Mooij - 1 month ago 11
SQL Question

How to combine sql queries where one query uses the MIN function

I have a read-only firebird database from which I want to extract data. In MS-Access it's very easy to get the data I want using three simple queries in which the output of each query is the input to the next one. Now I want to program this in PHP and to keep it on one line I want to combine these three queries in one but I can't figure it out.

The first query gives me all the dates starting today which are in my company planning (max 1000 lines). As some jobs are planned on more day's I only want the first date from today. I get this by using the MIN function in query 2. In the last query I use a left join to combine all my pending jobs with the generated first dates.

How can I do this in ONE query? As I only have reading rights I can't work with temporary tables.

Can someone give me some directions please?

**First step**
SELECT
salesorderplan.plandate,
salesorderplan.salesorder
FROM
salesorderplan
WHERE
salesorderplan.plandate >= Date();

**second step**
SELECT
Min([1estap].plandate) AS firstplandate, [1estap].salesorder
FROM
[1estap]
GROUP BY
[1estap].salesorder;

**third step**
SELECT
salesorder.orderno,
[2estap].firstplandate,
salesorder.description,
salesorder.deliverylocname,
salesorder.deliveryaddress,
salesorder.deliverycity
FROM
((salesorder)
left join 2estap on (salesorder.objectid = [2estap].salesorder))
WHERE
salesorder.salesstatus=1
ORDER BY
salesorder.orderno;


I have two tables, the first is called salesorder which contains all my jobs. The second is called salesorderplan which contains all the dates in my planning with the jobnumbers.

SALESORDER
objectid description etc
342567 blah blah
356782 jwz
384512 not in planning yet

SALESORDERPLAN
objectid salesorder plandate
23451 342567 12-03-2017
23489 342567 14-04-2017
23490 356782 13-03-2017
23496 356782 18-06-2017
23499 342567 21-08-2017
23499 342567 28-08-2017
23512 356782 30-08-2017
23524 356782 2-09-2017


I want a list of all my orders. If there is a date in my planning I want the first date after today (that is where I used the MIN fuct for)
So the outcome should be

Result
objectid description plandate
342567 blah blah 21-08-2017
356782 jwz 30-08-2017
384512 not in planning yet no date

Answer Source

I should preface this by saying I don't have experience with firebird, but I do have experience with SQL. This might work for you:

-- **third step**
SELECT
    salesorder.orderno,
    [2estap].firstplandate,
    salesorder.description,
    salesorder.deliverylocname,
    salesorder.deliveryaddress,
    salesorder.deliverycity,
FROM salesOrder LEFT JOIN (
    -- **second step**
    SELECT 
        Min([1estap].plandate) AS firstplandate, [1estap].salesorder
    FROM (
        -- **First step**
        SELECT
            salesorderplan.plandate,    
            salesorderplan.salesorder
        FROM
            salesorderplan
        WHERE
            salesorderplan.plandate >= Date()
    ) [1estap]
    GROUP BY 
        [1estap].salesorder
) [2estap] ON (salesorder.objectid = [2estap].salesorder))
WHERE
    salesorder.salesstatus=1
ORDER BY
    salesorder.orderno;

EDIT

As pointed out by @MarkRotteveel, your first two queries can be collapsed into one. Here's the query with that performed:

-- **third step**
SELECT
    salesorder.orderno,
    [2estap].firstplandate,
    salesorder.description,
    salesorder.deliverylocname,
    salesorder.deliveryaddress,
    salesorder.deliverycity,
FROM salesOrder LEFT JOIN (
    -- **first & second steps**
    SELECT 
        Min(plandate) AS firstplandate, salesorder
    FROM salesorderplan
    WHERE
        plandate >= Date()
    GROUP BY 
        salesorder
) [2estap] ON (salesorder.objectid = [2estap].salesorder))
WHERE
    salesorder.salesstatus=1
ORDER BY
    salesorder.orderno;