Query Help - Subquery/Exsists/?

[csmcvey]

I need help with the following query. I have two tables:

1) ORDERS(columns: fileid(pk), status, office, other fields)
2) INVOICES(columns: invoiceid(pk), fileid, status, date, amount, other fields)

I can have multiple invoices for an order.

I would like to select all the orders that match a certain condition and include a column in the results that tell if an invoice that meets certain conditions exsists for that record.

I have the following query for the ORDERS table:

Select fileid, office from ORDERS where status = 'closed' and office = 'New York'

The condition I need for my INVOICE table is: status = 'closed'

I am lost as how to do this, I don't know if I should use a CASE statement, use Exsists, subquery...

Any help that can be provided is appreciated.

 

[LFCfan]

Unless I'm mising something (untested code below):

select o.fileid, o.office from ORDERS o
join INVOICE i
on o.fielid = i.fileid
where o.status = 'closed'
and i.status = 'closed'
and o.office = 'New York'

~LFCfan

 

[csmcvey]

LFC:

Thank you for your reply.

That query will give me the orders that do have an invoice in a status of closed, but not give me the orders that meet the order conitions and have no invoice that is closed.

I need all the orders that meet the order coniditions (o.status = 'closed' & o.office = 'NY') and need to know which of those have invoices that are closed. Ideally it would be a column that says 'Invoie Finalized' or 'Invoice Not Finalized'

 

[tran008]

SELECT O.FILEID,
O.OFFICE,
I.INVOICEID,
(CASE
WHEN I.STATUS = 'closed' THEN 'Invoie
Finalized'
ELSE 'Invoice Not Finalized'
END) INVOICESTATUS
FROM ORDERS O
LEFT JOIN INVOICE I
ON O.FIELID = I.FILEID
WHERE O.STATUS = 'closed'
AND O.OFFICE = 'New York'