CODEWITHSUNDEEP
mysql
Sin5k4
Sin5k4

Reputation: 1626

Using subquery in a function

well,im sort of new to mysql im stuck with the following scenario:

    SELECT 
    h.machine_id,
    h.pin_no,
    h.created_at,
    (SELECT 
            created_at
        FROM
            history AS h1
        WHERE
            h1.created_at > h.created_at
                AND h1.machine_id = h.machine_id
                AND h1.pin_no = h.pin_no
                AND h1.state = 1
        LIMIT 1) AS ended_at,

TIMEDIFF(ended_at,created_at) as elapsed

FROM
    history AS h
WHERE
    h.state = 0

i would like to use the value from subquery in a function,such as datediff, how can i achieve that? i would use cte if it was mssql :)

Upvotes: 0

Views: 48

Answers (1)

siride
siride

Reputation: 209615

You just literally embed the subquery in the function as an argument. By the way, this doesn't really have to do with subqueries, but rather with the fact that you can't reuse expressions in the same SELECT list.

SELECT 
    h.machine_id,
    h.pin_no,
    h.created_at,
    (SELECT 
            created_at
        FROM
            history AS h1
        WHERE
            h1.created_at > h.created_at
                AND h1.machine_id = h.machine_id
                AND h1.pin_no = h.pin_no
                AND h1.state = 1
        LIMIT 1) AS ended_at,
   TIMEDIFF((SELECT 
            created_at
        FROM
            history AS h1
        WHERE
            h1.created_at > h.created_at
                AND h1.machine_id = h.machine_id
                AND h1.pin_no = h.pin_no
                AND h1.state = 1
        LIMIT 1),created_at) as elapsed
FROM
    history AS h
WHERE
    h.state = 0

If you really can't stand to use the subquery twice, you can use a derived table expression to calculate the subquery which can then be used multiple times in the outer query.

SELECT
    calculations.machine_id,
    calculations.pin_no,
    calculations.created_at,
    calculations.ended_at,
    TIMEDIFF(calculations.ended_at, calculations.created_at) AS elapsed
FROM (
    SELECT 
        h.machine_id,
        h.pin_no,
        h.created_at,
        (SELECT 
                created_at
            FROM
                history AS h1
            WHERE
                h1.created_at > h.created_at
                    AND h1.machine_id = h.machine_id
                    AND h1.pin_no = h.pin_no
                    AND h1.state = 1
            LIMIT 1) AS ended_at
    FROM
        history AS h
    WHERE
        h.state = 0
) AS calculations

Upvotes: 1

Related Questions