1

I'm having some trouble with a query where I have a table where I want to pick out items that have been marked with different fields.

Specifically, boxes on a conveyor that have moved around and been marked at different locations.

Below is what I have (it doesnt work - returns multiple records in inner select clause causing an error).

The field barcode is not unique. The way it is supposed to work is it will go around and for each time it passes certain areas a new record will be added for that barcode. I want to find the barcodes that have been to 3 different areas with one of them being lane_id LIKE(CRANE%_IN)

I know my error is a logical one but I'm having trouble getting my head around queries on the same table with sub queries.

As easy as the concept is (and I could do it in any language in 2 minutes) it needs to be done in SQL (postgres).

SELECT barcode, lane_id
FROM acts_divert_log
WHERE lane_id IN ('GTP01', 'GTP02', 'GTP03', 'GTP04')
AND divstat IN (0, 1)
AND barcode =
(
    SELECT barcode FROM acts_divert_log WHERE
    lane_id LIKE ('CRANE%_IN') 
    AND divstat IN (0, 1) 
    AND barcode =
    (
        SELECT barcode FROM acts_divert_log WHERE
        lane_id IN ('GTP01', 'GTP02', 'GTP03', 'GTP04')
        AND divstat IN (0, 1)
    ) 
);

The expected results should be unique barcodes which have been seen in 2 of the GTP0% areas and at least one CRANE%_IN area.

Any help or hints will be much appreciated. SQL really isn't my forte.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
solarflare
  • 423
  • 3
  • 14

3 Answers3

0

Try something like this:

SELECT barcode, lane_id
FROM acts_divert_log l
WHERE divstat IN (0, 1)
AND EXISTS (
  SELECT barcode
  FROM acts_divert_log t2
  WHERE divstat IN (0, 1)
  AND l.barcode = t2.barcode
  GROUP BY t2.barcode
  HAVING
  SUM(CASE WHEN lane_id IN ('GTP01', 'GTP02', 'GTP03', 'GTP04') THEN 1 ELSE 0 END) >= 2
  AND SUM(CASE WHEN lane_id LIKE ('CRANE%_IN') THEN 1 ELSE 0 END) >= 1
)
;
Serge
  • 3,986
  • 2
  • 17
  • 37
0

It's a case of relational division. There are many possible ways to solve this. One simple and fast approach:

SELECT barcode
     , a1.lane_id AS lane_id1
     , a2.lane_id AS lane_id2
     , a3.lane_id AS lane_id3
FROM   acts_divert_log a1
JOIN   acts_divert_log a2 USING (barcode)
JOIN   acts_divert_log a3 USING (barcode)
WHERE  a1.lane_id IN ('GTP01', 'GTP02', 'GTP03', 'GTP04')
AND    a2.lane_id IN ('GTP01', 'GTP02', 'GTP03', 'GTP04')
AND    a3.lane_id LIKE 'CRANE%_IN'
AND    a1.divstat IN (0, 1)
AND    a2.divstat IN (0, 1)
AND    a3.divstat IN (0, 1)
AND    a1.lane_id <> a2.lane_id;  -- assuming you want distinct lane_ids

We assembled and arsenal of techniques under this related question:

You need matching indexes to make this fast. Details depend on undisclosed information about table definition, data distribution, cardinalities etc.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • With the exception of the little typo at the top (a3.lane_id instead of a2.lane_id) it worked beautifully, thank you for this. – solarflare Oct 30 '17 at 03:07
0
Select distinct Barcode, lane_id from acts_divert_log
Where Barcode in (
Select barcode from (
Select Barcode, sum(cnt_gtp) as gto
, sum(cnt_crane) as crane
from (Select Barcode
, case when (lane_id in ('GTP01' , 'GTP02', 'GTP03', 'GTP04')) 
    Then 1 else 0 end as cnt_gtp, 
,  case when (lane_id like 'CRANE%_IN') then 1 else 0 end as cnt_crane
from acts_divert_log
Where divstat in (0,1)
and ((lane_id in ('GTP01' , 'GTP02', 'GTP03', 'GTP04'))  
   OR (lane_id like 'CRANE%_IN') ) 
) as aa
      Group by barcode
)  as bb
 Where gtp>=2 and crane>=1
 )
user1996915
  • 11
  • 2