SQL return only first match from join table

Question

I have a simple one to many table of jobs and statuses. Jobs can cycle back and forth in statuses during processing so a job could have more than one instance of a particular status. However, I need a report of that job as it hits just the first instance of particular statuses from start to finish.

My initially constructed statement will return the data I need but for every matching instance in the join--however, I only need the first match. For example: Job #, Date In, Invoice Date, Customer, OutForApproval Date, Approved Date, Due Date, Shipped Date.

The resulting data will return multiple rows from Table A (Jobs) but each join result, will only return the first match of OFA, Approved, Shipped, and Invoiced. Almost like a cross-tab report.

SELECT a."JobNumber" AS "Job Number", a."dateIn" AS "Date In", e."created" AS "Invoiced" , a."customer" AS "Customer", a."jobTitle" AS "Job Title", b."created" AS "OFA", c."created" AS "Approved", a."dateShip" AS "Date Due", d."created" AS "Shipped" 
FROM "@ Jobs" a
INNER JOIN "@ Statuses" b ON (a."ID"   = b."fkey" AND b."status" = 'OFA')
INNER JOIN "@ Statuses" c ON (b."fkey" = c."fkey" AND c."status" IN ('Offset Press','Indigo Press', 'Wide Format Press'))
INNER JOIN "@ Statuses" d ON (c."fkey" = d."fkey" AND d."status" IN ('Shipped', 'Partially Shipped'))
INNER JOIN "@ Statuses" e ON (d."fkey" = e."fkey" AND e."status" = 'Invoiced') 

WHERE a."currentStatus" = ? AND e."created" BETWEEN ? AND ?

ORDER BY a."JobNumber" ASC

It must conform to SQL 92. No MSSQL, Oracle or MySQL, no temp tables as this is not supported in FileMaker.

It would help to see a sample of the data and the output you are expecting. — Steve Mangiameli
@SteveMangiameli Example output would look like 55996,2015-12-30,2016-02-17 19:09:24,Bubba J’s Martial Arts,KICTASTICK FLYER,2016-01-19,2016-01-26,2016-02-04,2016-02-17 — Rob
Input would simply be that job's data and several rows of statuses--for now status table field has the fkey(foreign key), a status and date. — Rob

Nicolai Kant Nicolai Kant · Accepted Answer · 2016-06-07T22:44:40

Starting from FIleMaker 14 you can use "FETCH FIRST 1 ROWS ONLY"

SELECT a."JobNumber" AS "Job Number", a."dateIn" AS "Date In", e."created" AS "Invoiced" , a."customer" AS "Customer", a."jobTitle" AS "Job Title", b."created" AS "OFA", c."created" AS "Approved", a."dateShip" AS "Date Due", d."created" AS "Shipped" 
FROM "@ Jobs" a
INNER JOIN "@ Statuses" b ON (a."ID"   = b."fkey" AND b."status" = 'OFA')
INNER JOIN "@ Statuses" c ON (b."fkey" = c."fkey" AND c."status" IN ('Offset Press','Indigo Press', 'Wide Format Press'))
INNER JOIN "@ Statuses" d ON (c."fkey" = d."fkey" AND d."status" IN ('Shipped', 'Partially Shipped'))
INNER JOIN "@ Statuses" e ON (d."fkey" = e."fkey" AND e."status" = 'Invoiced')     
WHERE a."currentStatus" = ? AND e."created" BETWEEN ? AND ?    
ORDER BY a."JobNumber" ASC
FETCH FIRST 1 ROWS ONLY

Full specs:

http://help.filemaker.com/app/answers/detail/a_id/12061/~/specifying-offset-and-fetch-first-clauses-in-filemaker-pro-using-the-executesql

SQL return only first match from join table

1 Answers