current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I've got two tables in my database. One of them, 'orders', contains a set of columns with an integer which represents what the order should contain (like 5 of A and 15 of B). The second table, 'production_work', contains those same order columns, and a date, so whenever somebody completes part of an order, I track it.

So now i need a fast way to know which orders are completed, and I'm hoping to avoid a 'completed' table on the first column as orders are editable and it's just more logic to keep correct.

This query works, but it's horribly written. What's a better way to do this? There are actually 12 of these columns that go into this query...I'm just showing 3 of them for the example.

    SELECT *
    FROM orders o
    WHERE ud = (SELECT SUM(ud) FROM production_work WHERE order_id = o.ident)
            AND dp = (SELECT SUM(dp) FROM production_work WHERE order_id = o.ident)
            AND swrv = (SELECT SUM(swrv) FROM production_work WHERE order_id = o.ident)
share|improve this question

1 Answer 1

up vote 0 down vote accepted 
select o.*
from
    orders o
    inner join
    (
        select order_id, sum(ud) as ud, sum(dp) as dp, sum(swrv) as swrv
        from production_work
        group by order_id
    ) pw on o.ident = pw.order_id
where
    o.ud = pw.ud
    and o.dp = pw.dp
    and o.swrv = pw.swrv
share|improve this answer
    
Thank you, that's perfect! –  Gargoyle Aug 6 '13 at 19:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.