SQL Query that will omit records with the same ID based on values in another column

user_10552874 0
Viewed 64

* UPDATE*

Upon further review, the table I am using also has a linenumber column. See updated example data below. I feel like this could be extremely helpful in solving this....just not sure how. Sum up the line number by PO, and if it equals 1 it is a single line, if it is greater than 1 it is a multi line....does that do anything for us?

New here and to SQL so please forgive my ignorance. Hopefully this is an easy answer.

Looking to build 3 similar queries that will return purchase orders that contain more than 1 item and:

  1. Contain NO Lot Controlled Items
  2. Contain ALL Lot Controlled Items
  3. Contain a MIX of Lot Controlled and Non-Lot Controlled Items

Data looks like this...

PONUMBER    ITEMNUMBER  LOTCONTROLLED  LINENUMBER
PO1.18      OSC1024     0              1
PO1.18      OSC1025     0              2
PO1.18      OSC1026     0              3
PO1.2       OSC1199     0              1
PO1.2       OSC1200     1              2
PO1.21      OSC1201     1              1
PO1.21      OSC1202     1              2
PO1.22      OSC1203     1              1
PO1.23      OSC1204     1              1
PO1.23      OSC1205     0              2
PO1.24      OSC1206     1              1
PO1.24      OSC1207     1              2
PO1.24      OSC1300     0              3

Query for NO Lot Controlled items works great...

    SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` = 0
        GROUP BY `PONUMBER`
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER`

I thought it would be as simple as changing the WHERE LOTCONTROLLED to be = 1, to get Purchase Orders with ALL Lot Controlled items, but that returns some Purchase Orders that have mixed lines as well.

How can I eliminate a purchase order from inclusion if any one of the lines are not lot controlled?

select mysql sql
4 Answers

I like using NOT EXISTS here:

SELECT POD.*
FROM table1 POD
JOIN (SELECT PONUMBER
      FROM table1 POD
      WHERE NOT EXISTS (SELECT *
                        FROM table1 POD1
                        WHERE POD.PONUMBER = POD1.PONUMBER
                        AND POD1.LOTCONTROLLED = 1)
      GROUP BY PONUMBER
      HAVING COUNT(*) > 1
      ) POD1 ON POD.PONUMBER = POD1.PONUMBER

This will omit the PONUMBER from results if any record from that PONUMBER has LOTCONTROLLED = 1 or 0, depending on what you put in the exists subquery.

To get only records that have a mix, you can use COUNT().. HAVING:

SELECT PONUMBER,
       ITEMNUMBER,
       LOTCONTROLLED
FROM table1 POD
JOIN (SELECT PONUMBER
      FROM table1
      GROUP BY PONUMBER
      HAVING COUNT(DISTINCT LOTCONTROLLED) = 2
     ) POD1 ON POD.PONUMBER = POD1.PONUMBER
user_54200080

Looks like you also need to join the queries by Lot Controlled too, so I added it to the Group By and Inner Select so it could be joined:

NO LOT CONTROLLED:

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, 'LOTCONTROLLED', COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` = 0
        GROUP BY `PONUMBER`, 'LOTCONTROLLED'
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER` AND `POD`.`LOTCONTROLLED` = `POD1`.`LOTCONTROLLED`

LOT CONTROLLED:

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, 'LOTCONTROLLED', COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` = 1
        GROUP BY `PONUMBER`, 'LOTCONTROLLED'
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER` AND `POD`.`LOTCONTROLLED` = `POD1`.`LOTCONTROLLED`

ALL LOT CONTROLLED:

SELECT 
      `POD`.`PONUMBER`,
      `POD`.`ITEMNUMBER`,
      `POD`.`LOTCONTROLLED`
    FROM
      table1 AS `POD`
    INNER JOIN
      (
        SELECT `PONUMBER`, 'LOTCONTROLLED', COUNT(`PONUMBER`)
        FROM table1
        WHERE `LOTCONTROLLED` IN (0,1)
        GROUP BY `PONUMBER`, 'LOTCONTROLLED'
        HAVING (COUNT(`PONUMBER`) > 1)
       ) as `POD1`
     ON `POD`.`PONUMBER` = `POD1`.`PONUMBER` AND `POD`.`LOTCONTROLLED` = `POD1`.`LOTCONTROLLED`
user_105529280

Window functions are the simplest method, but you probably don't have those. So, just use the min() and max() of lotcontrolled. The basic query is:

select pod.* 
from table1 pod join
      (select ponumber, min(lotcontrolled) as min_lc, max(lotcontrolled) as max_lc
       from table1 pod
       group by ponumber
       having count(*) > 1
      ) p
      using (ponumber)

Then your three conditions are:

max_lc = 0  -- no lot controlled
min_lc = 1  -- all lot controlled 
min_lc <> max_lc  -- mixed

Some people might prefer the more verbose versions:

min_lc = max_lc and max_lc = 0  -- no lot controlled
min_lc = max_lc and max_lc = 1  -- all lot controlled 
min_lc <> max_lc  -- mixed
user_11440350

try something like this:

    --No items in the group contain LotControlled 
    SELECT * 
    FROM   your_table 
    WHERE  ponumber IN (SELECT ponumber 
                        FROM   your_table 
                        GROUP  BY ponumber 
                        HAVING Sum(CONVERT(INT, lotcontrolled)) = 0) 

    --All Items Contain 
    SELECT * 
    FROM   your_table 
    WHERE  ponumber IN (SELECT ponumber 
                        FROM   your_table 
                        GROUP  BY ponumber 
                        HAVING Sum(CONVERT(INT, lotcontrolled)) = Count(*)) 

    --mixed 
    SELECT * 
    FROM   your_table 
    WHERE  ponumber IN (SELECT ponumber 
                        FROM   your_table 
                        GROUP  BY ponumber 
                        HAVING Sum(CONVERT(INT, lotcontrolled)) != Count(*) 
                               AND Sum(CONVERT(INT, lotcontrolled)) > 0)
user_100611110
Related