Hi Team,
   D10.2.3, EDB 2.28.

   I have the following Query SQL code which works.
{sql}
select * from lineitems
where (buscode = 'Z') or (Buscode = :BusCode)or (Buscode = 'D')
order by JobNo, PartNo
{sql
   An associated table is JobTickets with a common field of JobNo.

   There may be 1 or more Line Items per JobNo.

   I need to limit the records viewed in the above query to only those that their relevant Jobticket does not have a
jobstatus of 'CA', CO' or 'CL'.

   Any assistance appreciated.

Regards & TIA,
Ian

Ian


I don't have any data in LineItems to test agains but this should do the job

select *
from lineitems
where
((buscode = 'Z') or (Buscode = :BusCode)or (Buscode = 'D'))
AND
JobNo IN (SELECT JobNo FROM JobTickets WHERE (NOT JobStatus IN ('CA','CO', 'CL')) OR JobStatus IS NULL)
order by JobNo, PartNo

A JOIN may be faster but I often find they have unexpected consequences (ie I hadn't thought it through properly) in terms of which rows are selected


Roy Lambert

Hi Roy,
   Thanks for that.  Works perfectly.

Regards,
Ian