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_I D
TRANSACTION_DAT E

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_DAT E
from WORK_ORDER W, LABOR_TICKET LT
where W.BASE_ID||W.LO T_ID =
LT.WORKORDER_BA SE_ID||LT.WORKO RDER_LOT_ID
and lt.TRANSACTION_ DATE = (select TRANSACTION_DAT E from LABOR_TICKET LT, WORK_ORDER W where
W.BASE_ID = LT.WORKORDER_BA SE_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_DAT E
from WORK_ORDER W, LABOR_TICKET LT
where W.BASE_ID||W.LO T_ID =
LT.WORKORDER_BA SE_ID||LT.WORKO RDER_LOT_ID
and lt.TRANSACTION_ DATE in (
select TRANSACTION_DAT E
from LABOR_TICKET LT, WORK_ORDER W
where W.BASE_ID = LT.WORKORDER_BA SE_ID
and TRANSACTION_DAT E = lt.TRANSACTION_ DATE )