SELECT QUERY??

nikko50

Hi all. I have a orders table and a order_details table. I'm trying to locate the orders that are still "ACTIVE" in the orders table that do NOT have itemid "1111" on them in the order_details table. How can I do this?

laserlight

It depends on your exact database schema, what columns you want to select, etc, but it could be something like:

SELECT * FROM orders, order_details
    WHERE orders.id=order_details.id
        AND orders.active=1
        AND order_details.itemid<>1111

Basically, join the two tables on some join condition, then assert the requirements.

nikko50

Hi. I tried the below query but am picking up ALL the active orders and not just the orders that don't contain the equipmentid of "1111". How can I do this. Please help🙁

SELECT DISTINCT orders.orderid
FROM orders order_details
WHERE orders.orderid = order_details.orderid
AND orders.status = "ACTIVE"
AND order_details.equipmentid <>1111

laserlight

What is your relevant database schema?

nikko50

The "orderid" is the relation between the two tables "orderid" and "order_details". This is a one to many relationship.

laserlight

The "orderid" is the relation between the two tables "orderid" and "order_details". This is a one to many relationship.

The schema is everything, not just the foreign keys and relationship arity.

You appear to have aliased the orders table as order_details instead of performing a join. The correct query should be:

SELECT DISTINCT orders.orderid
    FROM orders, order_details
    WHERE orders.orderid = order_details.orderid
        AND orders.status = 'ACTIVE'
        AND order_details.equipmentid<>1111

If equipmentid is actually a string type instead of a numeric type, you should use order_details.equipmentid<>'1111'.

nikko50

I tried the below but still am picking up all the order that are active instead of the active orders that are missing equipmentid "1111". I must be doing something wrong.

SELECT DISTINCT orders.orderid
FROM orders, order_details
WHERE orders.orderid = order_details.orderid
AND orders.status = "ACTIVE"
AND order_details.equipmentid<>1111

nikko50

Thanks for the hellp. The below query worked.
SELECT orders.orderid
FROM orders
LEFT OUTER
JOIN order_details
ON order_details.orderid = orders.orderid
AND order_details.equipmentid = 1111
WHERE orders.status = 'ACTIVE'
AND order_details.orderid IS NULL

laserlight

I tried the below but still am picking up all the order that are active instead of the active orders that are missing equipmentid "1111".

Oh, I understand now. Just having order_details.equipmentid<>1111 does not work as an order may also have some other equipmentid in a related order detail row.

One possible solution is to use a subquery, e.g.,

SELECT DISTINCT orderid
    FROM orders
    WHERE status = 'ACTIVE'
        AND orderid NOT IN
            (SELECT DISTINCT orders.orderid
                FROM orders, order_details
                    WHERE orders.orderid = order_details.orderid
                        AND orders.status = 'ACTIVE'
                        AND order_details.equipmentid = 1111)

.By the way, note that single quotes are used to delimit strings. Double quotes are used to delimit identifiers, though for some database management systems they are allowed to delimit strings as well.

nikko50

Thanks for all the help laserlight!!

laserlight

No problem. Remember to mark this thread as resolved (if it is, e.g., you might want someone else to propose an even more efficient solution).