Problem with sub query

mongoose00318 · March 24, 2021

I am trying to use this to reference another table to determine if an item has shipped or not; if it has don't include it. But I'm having some issues with my SQL.

SELECT
    pd.enterprise,
    COUNT(*) AS total
FROM
    production_data pd
WHERE
    enterprise = "EXXON" AND as400_ship_date = CAST("2021-03-02" AS DATE) AND hold_date = "0000-00-00" AND pd.id NOT IN(
    SELECT
        order_id
    FROM
        production_status
    WHERE
        order_id = pd.id AND dept_code = 13 AND status_id = 3
    ORDER BY
        id
    DESC
LIMIT 1
)

I think the problem is where I have order_id = pd.id

Do I need to provide table structures or is this some type of syntax error?

Edited March 24, 2021 by mongoose00318
Updated my code; still same problem though

mongoose00318 · March 24, 2021

Shoot, well MariaDB doesn't support LIMIT in a subquery. Is there a different way to accomplish this?

mongoose00318 · March 24, 2021

SELECT
    pd.enterprise,
    COUNT(*) AS total
FROM
    production_data pd
WHERE
    enterprise = "EXXON" AND as400_ship_date = CAST("2021-03-02" AS DATE) AND hold_date = "0000-00-00" AND pd.id NOT IN(
    SELECT
        order_id
    FROM
        (
        SELECT
            MAX(id),
            order_id
        FROM
            production_status
        WHERE
            order_id = pd.id AND dept_code = 13 AND status_id = 3
    )
)

Okay I changed it to this but am still getting a syntax error.

Barand · March 24, 2021

1 hour ago, mongoose00318 said:

Shoot, well MariaDB doesn't support LIMIT in a subquery

Are you sure? Last time I used MariaDB I had to use a LIMIT clause (with a huge number (2⁶⁴ - 1) to force it to write a subquery to a temp table, otherwise it wouldn't accept an ORDER BY clause.

E.G. ORDER BY id LIMIT 18446744073709551615

Your query will be more efficent if you use a JOIN rather than the "NOT IN subquery".

If you post a dump with test data I'll have go at rewriting it for you.

Edited March 24, 2021 by Barand

mongoose00318 · March 24, 2021

@Barand Yes I'm sure about the Maria problem. I may just have an older version. It has said this to me before.

Here is some sample data:

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Mar 24, 2021 at 12:02 PM
-- Server version: 10.4.10-MariaDB
-- PHP Version: 7.3.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `fedheath_production`
--

-- --------------------------------------------------------

--
-- Table structure for table `production_data`
--

DROP TABLE IF EXISTS `production_data`;
CREATE TABLE IF NOT EXISTS `production_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_number` int(8) NOT NULL,
  `enterprise` tinytext NOT NULL,
  `part_number` text NOT NULL,
  `description` text NOT NULL,
  `psm` tinytext NOT NULL,
  `qty` int(11) NOT NULL,
  `line_item` varchar(11) NOT NULL,
  `as400_ship_date` date DEFAULT NULL,
  `hold_reason` text NOT NULL DEFAULT '0',
  `hold_date` date DEFAULT NULL,
  `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
  PRIMARY KEY (`id`),
  KEY `job_line` (`job_number`,`line_item`)
) ENGINE=MyISAM AUTO_INCREMENT=16615 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `production_data`
--

INSERT INTO `production_data` (`id`, `enterprise`, `job_number`) VALUES
(15290, 'EXXON', 22113902),
(15291, 'EXXON', 22113902),
(15292, 'EXXON', 22113902),
(15293, 'EXXON', 22113902),
(15294, 'EXXON', 22113902),
(15296, 'EXXON', 22113902),
(15297, 'EXXON', 22113902),
(15298, 'EXXON', 22113902),
(15299, 'EXXON', 22113902),
(15300, 'EXXON', 22113902),
(15301, 'EXXON', 22113902),
(15302, 'EXXON', 22113902),
(15303, 'EXXON', 22113902),
(15304, 'EXXON', 22113902),
(16601, 'EXXON', 22113902),
(15154, 'EXXON', 22111012),
(15157, 'EXXON', 22111012),
(15158, 'EXXON', 22111012),
(16614, 'EXXON', 22113902),
(16613, 'EXXON', 22113902),
(16610, 'EXXON', 22113902),
(16609, 'EXXON', 22113902),
(16602, 'EXXON', 22113902),
(16605, 'EXXON', 22113902),
(16589, 'EXXON', 22113902),
(16598, 'EXXON', 22113902),
(16594, 'EXXON', 22113902),
(16593, 'EXXON', 22113902),
(16590, 'EXXON', 22113902),
(16606, 'EXXON', 22113902),
(16597, 'EXXON', 22113902),
(15156, 'EXXON', 22111012);
COMMIT;

Here is the data for the production_status table:

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Mar 24, 2021 at 12:03 PM
-- Server version: 10.4.10-MariaDB
-- PHP Version: 7.3.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `fedheath_production`
--

-- --------------------------------------------------------

--
-- Table structure for table `production_status`
--

DROP TABLE IF EXISTS `production_status`;
CREATE TABLE IF NOT EXISTS `production_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `submit_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time user submits status change',
  `expected_complete_time` date DEFAULT NULL COMMENT 'expected completion time which user enters',
  `dept_code` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=49402 DEFAULT CHARSET=latin1 COMMENT='Status Types [ 0: Not Started, 1: In Progress, 2: Delayed, 3: Finished ]';

--
-- Dumping data for table `production_status`
--

INSERT INTO `production_status` (`id`, `order_id`, `user_id`, `status_id`, `submit_time`, `expected_complete_time`, `dept_code`) VALUES
(49285, 15298, 30, 3, '2021-03-23 19:03:59', NULL, 13),
(49192, 15298, 43, 3, '2021-03-23 12:53:52', NULL, 11),
(49068, 15298, 43, 1, '2021-03-22 16:17:00', '2021-03-24', 11),
(47659, 15298, 23, 3, '2021-03-08 16:42:17', NULL, 7),
(46873, 15298, 23, 1, '2021-03-01 19:13:17', '2021-03-03', 7);
COMMIT;

Will this be enough?

Barand · March 24, 2021

Your query selects on as400_ship_date and hold_date. That production_data contains no date data therefore no records will be selected.

mongoose00318 · March 24, 2021

Shoot. One second. Sorry about that.

mongoose00318 · March 24, 2021

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Mar 24, 2021 at 12:21 PM
-- Server version: 10.4.10-MariaDB
-- PHP Version: 7.3.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `fedheath_production`
--

-- --------------------------------------------------------

--
-- Table structure for table `production_data`
--

DROP TABLE IF EXISTS `production_data`;
CREATE TABLE IF NOT EXISTS `production_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_number` int(8) NOT NULL,
  `enterprise` tinytext NOT NULL,
  `part_number` text NOT NULL,
  `description` text NOT NULL,
  `psm` tinytext NOT NULL,
  `qty` int(11) NOT NULL,
  `line_item` varchar(11) NOT NULL,
  `as400_ship_date` date DEFAULT NULL,
  `hold_reason` text NOT NULL DEFAULT '0',
  `hold_date` date DEFAULT NULL,
  `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted',
  PRIMARY KEY (`id`),
  KEY `job_line` (`job_number`,`line_item`)
) ENGINE=MyISAM AUTO_INCREMENT=16615 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `production_data`
--

INSERT INTO `production_data` (`id`, `job_number`, `enterprise`, `part_number`, `description`, `psm`, `qty`, `line_item`, `as400_ship_date`, `hold_reason`, `hold_date`, `insert_time`) VALUES
(15290, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15291, 22113902, 'EXXON', 'LD0008RM.ABEX', 'LED RF REMOTE CONTROLLER KIT ABLE||EIGHT BUTTON WIRELESS REMOTE||DIRECT||EXXON MOBIL ONLY||', 'S', 1, 'J', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15292, 22113902, 'EXXON', 'LD0120GR.2ABEX', 'LED 12\" GRN 1-PROD MOD(2 MOD) ABLE NO RM||SEVEN SEGMENT DOES NOT INCLUDE REMOTE||EXXON MOBIL ONLY||', 'S', 1, 'I', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15293, 22113902, 'EXXON', 'LD0120RD.2ABEX', 'LED 12\" RED 1-PROD MOD(2 MOD) ABLE NO RM||SEVEN SEGMENT REMOTE NOT INCLUDED||EXXON MOBIL ONLY||', 'S', 1, 'H', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15294, 22113902, 'EXXON', 'LD0180RD.2ABEX', 'LED 18\" RED 1-PROD MOD(2 MOD) ABLE NO RM||SEVEN SEGMENT REMOTE NOT INCLUDED||EXXON MOBIL ONLY||', 'S', 1, 'G', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15296, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||7 1/2\" X 53 1/2\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||* REGULAR SELECT MOBIL CARDS - DWG 210801.PP*||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||', '', 2, 'N', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15297, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15298, 22113902, 'EXXON', 'XM0135FM.0VNFPR', 'EXXONMOBIL 13\'4x5 VC DF FM SGN 9PNL 24V||(NO FACES)||PRINCIPAL PINN 24V ILLUMINATION||** LOAD ORDER: 2X NOURIA, 2X REGULAR||SELECT MOBIL CARDS, 1X REGULAR, 1X||DIESEL EFFICIENT, 1X SYNERGY, 1X DUNKIN,||1X SUBWAY **||', 'M', 1, 'C', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15299, 22113902, 'EXXON', 'XM3015FA.0VB', 'EXXONMOBIL 1\'6x5 VC FACE TYPE B NO CRATE||** SYNERGY - DWG 150805 **||', 'M', 2, 'K', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15300, 22113902, 'EXXON', 'XM3015FA.0VB', 'EXXONMOBIL 1\'6x5 VC FACE TYPE B NO CRATE||** DUNKIN - DWG 207346 **||', 'M', 2, 'L', '2021-03-02', 'FF', '0000-00-00', '2021-03-08 12:05:20'),
(15301, 22113902, 'EXXON', 'XM3015FA.0VB', 'EXXONMOBIL 1\'6x5 VC FACE TYPE B NO CRATE||** SUBWAY - DWG 187262 **||', 'M', 2, 'M', '2021-03-02', 'FF', '0000-00-00', '2021-03-08 12:05:20'),
(15302, 22113902, 'EXXON', 'XM3035FA.NOURIA', 'EXXONMOBIL 3x5 NOURIA FACE FOR TYPE B||ID 30\"CTS PSH THRU SF SGN ON FLAT ALUM||FACE. NEED DIGITAL PRINT* GRA EX210804||DWG EX3035FM_NOURIA||** NOURIA - DWG 210804 **||', 'M', 2, 'D', '2021-03-02', '', '0000-00-00', '2021-02-22 13:05:19'),
(15303, 22113902, 'EXXON', 'XM9005FA.1NLDAB', 'EXXONMOBIL 1\'6X5 1PR ABLE FACE FOR SIGNS||(TO USE 12\" ABLE LED) FOR RED OR GREEN||LEDS OR REMOTE NOT INCLUDED||INCLUDES LABOR AND AL ANG TO INSTALL LED||** (2) REGULAR, (2) DIESEL EFFICIENT||(BLUE)(SLIP IN PRODUCT PANELS ALL GRADES||) - DWG 210802 **||', 'M', 4, 'F', '2021-03-02', 'FF', '0000-00-00', '2021-03-01 14:35:19'),
(15304, 22113902, 'EXXON', 'XM9005FA.1NLDAB2X', 'EXXONMOBIL 3X5 1-PR ABLE FACE FOR SIGNS||(TO USE 18\" ABLE LED) FOR RED OR GREEN||LEDS OR REMOTE NOT INCLUDED||INCLUDES LABOR AND AL ANG TO INSTALL LED||** REGULAR SELECT MOBIL CARDS (SLIP IN||PRODUCT PANELS ALL GRADES) - DWG||210801 **||', 'M', 2, 'E', '2021-03-02', 'FF', '0000-00-00', '2021-03-19 11:35:20'),
(16601, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 13:35:28'),
(15154, 22111012, 'EXXON', 'DP3000MS.2130', 'DIGITAL PRINT 21SF-30SF||** NOURIA - DWG 210294.DP **||', 'S', 2, 'D', '2021-03-02', '', '0000-00-00', '2021-02-12 13:35:16'),
(15157, 22111012, 'EXXON', 'TOOLING', 'TOOLING||** TO EMBOSS MOBIL ID **||', 'M', 543, 'B', '2021-03-02', '', '0000-00-00', '2021-02-12 13:35:16'),
(15158, 22111012, 'EXXON', 'XM9100CP.CLSP', 'XOM 11X10 CP SIGN BASE CLADDING(CSTM)||ALUMINUM WRAP||EX0100CP_3LDNOURIAVPN||** DWG 210294 **||', 'M', 1, 'C', '2021-03-02', 'WO', '0000-00-00', '2021-02-19 03:05:21'),
(16614, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 15:05:22'),
(16613, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 15:05:22'),
(16610, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:35:25'),
(16609, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:35:25'),
(16602, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 13:35:28'),
(16605, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:05:23'),
(16589, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 16:05:28'),
(16598, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 11:35:28'),
(16594, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 21:05:30'),
(16593, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 21:05:30'),
(16590, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-22 16:05:28'),
(16606, 22113902, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:     000.00||12\" X 18\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR, (2) DIESEL EFFICIENT - DWG||210802.PP **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||BW - GLOBAL||', '', 4, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 14:05:23'),
(16597, 22113902, 'EXXON', 'DP3000MS.1120', 'DIGITAL PRINT 11SF-20SF||** NOURIA - DWG 210804.DP **||', 'S', 2, 'O', '2021-03-02', '', '0000-00-00', '2021-03-23 11:35:28'),
(15156, 22111012, 'EXXON', 'PRODUCT', 'PRODUCT PANELS           PRICE:      65.00||5\" X 44 1/2\" PRODUCT PANELS||CLEAR COPY/BLUE BACKGROUND||EMPRINT SEMIBOLD FONT||** (2) REGULAR SELECT MOBIL CARDS,||(2) REGULAR, (2) DIESEL EFFICIENT - DWG||210294 **||** JV TO PURCHASE: DO NOT MANUFACTURE: PLEASE||SEND WITH THE ORDER **||', '', 6, 'E', '2021-03-02', '', '0000-00-00', '2021-02-12 13:35:16');
COMMIT;

Here ya go. Sorry about that.

Barand · March 24, 2021

From that data, what are the expected results?

So I know if I get it right.

mongoose00318 · March 24, 2021

This should item should come back

image.png.8af85df726faaafb3acc70c69ed215fc.png

Because order_id 15298 has an entry in the production_status table where its dept_code is = 13 and it's status_id = 3

mongoose00318 · March 24, 2021

I only have interest in the order_id and knowing that it exists in that production_status table with those conditions (meaning it's been marked by the shipping dept as shipped)

Barand · March 24, 2021

The query returns

SELECT
    pd.enterprise,
    COUNT(*) AS total

The enterprise columns are all EXXON but what should the count be?

I get 31 as there are 32 records but 1 has a status where its dept_code is = 13 and it's status_id = 3, therefore excluded.

Is that what you expect?

mongoose00318 · March 24, 2021

I believe so. Can you show the whole query so I can test it?

Barand · March 24, 2021

OK. I wanted to know if I was on the right track.

SELECT
    pd.enterprise,
    COUNT(*) AS total
FROM
    production_data pd
	LEFT JOIN
    production_status ps ON ps.order_id = pd.id
                        AND ps.dept_code = 13
                        AND ps.status_id = 3
WHERE pd.enterprise = "EXXON" 
      AND pd.as400_ship_date = '2021-03-02' 
      AND pd.hold_date = "0000-00-00"
      AND ps.order_id IS NULL;

mongoose00318 · March 24, 2021

Okay, can you explain that one to me a bit? So, is the AND ps.order_id IS NULL the part that is basically saying only order that do not exist in the production_status table with these parameters?

mongoose00318 · March 24, 2021

@Barand Very cool though works like a charm.

mongoose00318 · March 24, 2021

And I learned something new!

Barand · March 24, 2021

9 minutes ago, mongoose00318 said:

Okay, can you explain that one to me a bit? So, is the AND ps.order_id IS NULL the part that is basically saying only order that do not exist in the production_status table with these parameters?

If we do a slightly different query

SELECT 
      pd.id
    , pd.enterprise
    , pd.as400_ship_date
    , pd.hold_date
    , ps.order_id
    , ps.dept_code
    , ps.status_id 
FROM
    production_data pd
    LEFT JOIN
    production_status ps ON ps.order_id = pd.id
                        AND ps.dept_code = 13
                        AND ps.status_id = 3
WHERE pd.enterprise = "EXXON" 
      AND pd.as400_ship_date = '2021-03-02' 
      AND pd.hold_date = "0000-00-00";
      
+-------+------------+-----------------+------------+----------+-----------+-----------+
| id    | enterprise | as400_ship_date | hold_date  | order_id | dept_code | status_id |
+-------+------------+-----------------+------------+----------+-----------+-----------+
| 15298 | EXXON      | 2021-03-02      | 0000-00-00 |    15298 |        13 |         3 |
| 15154 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15156 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15157 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15158 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15290 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15291 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15292 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15293 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15294 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15296 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15297 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15299 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15300 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15301 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15302 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15303 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15304 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16589 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16590 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16593 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16594 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16597 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16598 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16601 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16602 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16605 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16606 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16609 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16610 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16613 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16614 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
+-------+------------+-----------------+------------+----------+-----------+-----------+
32 rows in set (0.00 sec)

Becasue it uses a LEFT JOIN, we get nulls where there was no matching record meeting the join criteria. These are the ones we want to count hence the ps.order_id IS NULL

mongoose00318 · March 24, 2021

18 minutes ago, Barand said:

If we do a slightly different query


SELECT 
      pd.id
    , pd.enterprise
    , pd.as400_ship_date
    , pd.hold_date
    , ps.order_id
    , ps.dept_code
    , ps.status_id 
FROM
    production_data pd
    LEFT JOIN
    production_status ps ON ps.order_id = pd.id
                        AND ps.dept_code = 13
                        AND ps.status_id = 3
WHERE pd.enterprise = "EXXON" 
      AND pd.as400_ship_date = '2021-03-02' 
      AND pd.hold_date = "0000-00-00";
      
+-------+------------+-----------------+------------+----------+-----------+-----------+
| id    | enterprise | as400_ship_date | hold_date  | order_id | dept_code | status_id |
+-------+------------+-----------------+------------+----------+-----------+-----------+
| 15298 | EXXON      | 2021-03-02      | 0000-00-00 |    15298 |        13 |         3 |
| 15154 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15156 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15157 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15158 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15290 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15291 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15292 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15293 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15294 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15296 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15297 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15299 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15300 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15301 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15302 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15303 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 15304 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16589 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16590 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16593 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16594 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16597 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16598 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16601 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16602 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16605 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16606 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16609 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16610 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16613 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
| 16614 | EXXON      | 2021-03-02      | 0000-00-00 |     NULL |      NULL |      NULL |
+-------+------------+-----------------+------------+----------+-----------+-----------+
32 rows in set (0.00 sec)

Becasue it uses a LEFT JOIN, we get nulls where there was no matching record meeting the join criteria. These are the ones we want to count hence the ps.order_id IS NULL

Awesome man. You're level of SQL knowledge is impressive. Thanks for the thorough explanation. Very cool indeed.

Sign In

Problem with sub query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information