SQL Statement - Join

[itvixen7410]

Hi,

I'm having trouble creating a Crystal report. Usually when this happens I go back to Query Analyzer and try to get the results I want using SQL. I can't even get that this time. Hoping someone knows how to do this. :)

I have two tables, a WORK_ORDER table and LABOR_TICKET table. The goal is to find a released WORK_ORDER that hasn't had a labor ticket against it in the last 2 weeks. So no work orders will fall between the cracks.

WORK_ORDER table has these fields:
BASE_ID
LOT_ID
STATUS
TYPE

The BASE_ID and LOT_ID make up the primary key.

The LABOR_TICKET table has these fields:
ID
WORKORDER_BASE_ID
WORKORDER_LOT_ID
TRANSACTION_DATE

I want to find all of the WORK_ORDERS that haven't had a labor ticket against them in the last two weeks.

I tried something like this:

Select W.BASE_ID||' '||W.LOT_ID,LT.TRANSACTION_DATE
from WORK_ORDER W, LABOR_TICKET LT
where W.BASE_ID||W.LOT_ID =
LT.WORKORDER_BASE_ID||LT.WORKORDER_LOT_ID
and lt.TRANSACTION_DATE = (select TRANSACTION_DATE from LABOR_TICKET LT, WORK_ORDER W where
W.BASE_ID = LT.WORKORDER_BASE_ID);

but the sub-select is only grabbing one record - not the max trans date for each work order base + lot.

I'd love some help.

Thanks!

[lottalava]

You must use [b]in clause

[lottalava]

Select W.BASE_ID||' '||W.LOT_ID,LT.TRANSACTION_DATE
from WORK_ORDER W, LABOR_TICKET LT
where W.BASE_ID||W.LOT_ID =
LT.WORKORDER_BASE_ID||LT.WORKORDER_LOT_ID
and lt.TRANSACTION_DATE in (
select TRANSACTION_DATE
from LABOR_TICKET LT, WORK_ORDER W
where W.BASE_ID = LT.WORKORDER_BASE_ID
and TRANSACTION_DATE = lt.TRANSACTION_DATE )