MySQL Join with Count Embedded?

[mongoose00318]

I'm trying to figure out a way to do this all in one SQL statement. Here's the two table structures:

CREATE TABLE `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,
 `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=1789 DEFAULT CHARSET=latin1

CREATE TABLE `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=3523 DEFAULT CHARSET=latin1 COMMENT='Status Types [ 0: Not Started, 1: In Progress, 2: Delayed, 3: Finished ]'

Here is some simple code that demonstrates what I am trying to do:

$sql = "
		SELECT *
		FROM production_status
		WHERE dept_code = 13 AND status_id = 3
		ORDER BY order_id
";

$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll();
$rowCount = $statement->rowCount();

$sql = "SELECT * FROM production_data";
$statement = $pdo->prepare($sql);
$statement->execute();
$orders = $statement->fetchAll();

//store shipped orders in array
foreach ( $results as $r ) 
	$shipped[$r['order_id']] = TRUE;

//store only unshipped orders in array
foreach ( $orders as $o ) 
	if ( !isset ( $shipped[ $o['id'] ] ) ) 
		$orders_show[] = $o;

echo 'Num records: ' . count($orders_show);
echo '<pre>';
print_r($shipped);

I am trying to not show any orders that have a shipped status (dept_code = 13 & status_id = 3)...but I need to do it in the query instead of like I am doing above because of the pagination with tabulator. Here is an example of the first query it does when I hit the page: 

SELECT id, job_number, enterprise, description, qty, line_item, as400_ship_date, hold_date, insert_time
FROM production_data 
ORDER BY enterprise, job_number, line_item 
LIMIT 0, 14"

If I then pull any orders out of the dataset which is returned by that query I don't have 14 records for each page. So is there some way to just do it all in just one query...maybe using a join and count?

[mongoose00318]

I need to do the opposite of this: 

SELECT 
    pd.*,
    ps.*
FROM `production_data`AS pd
LEFT JOIN production_status AS ps ON ps.order_id = pd.id
	AND ps.dept_code = 13
        AND ps.status_id = 3

Basically, if there are no records in production_status where dept_code = 13 and status_id = 3 then include the record in the query...

[benanamen]

Allow me to introduce you to the NOT conditional.

https://www.techonthenet.com/mysql/not.php

[mongoose00318]

Ah yes okay I tried that with a subquery but I'm still not getting the right results:

SELECT 
    pd.*,
    ps.*
FROM `production_data`AS pd
LEFT JOIN production_status AS ps ON ps.order_id = pd.id
WHERE NOT EXISTS (SELECT * FROM production_status
				  WHERE ps.dept_code = 13 AND ps.status_id = 3)

Is there a way to do a record count of the sub query and then tell it to only show the records with 0 records?

It returns all of the other records in production_status except those with a dept_code = 13 and status_id = 3. I just need to know if the order has a record in production_status of dept_code = 13 and status_id = 3.

I guess I could just remove all records from the production_data table which are shipped and store them in a separate table.

Edited June 8, 2020 by mongoose00318

[requinix]

Are you trying to exclude rows from production_data that have any 13/3 rows in production_status? Or just not include the 13/3 rows in the results?

[mongoose00318]

2 minutes ago, requinix said:

Are you trying to exclude rows from production_data that have any 13/3 rows in production_status? Or just not include the 13/3 rows in the results?

I'm trying to exclude any rows from production_data that have any 13/3 rows in production_status.

[requinix]

Do your query like you would normally do if it didn't have that condition. I don't know if you want that JOIN to be LEFT or not.

Then add another LEFT JOIN to production_status like you'll already have, except it has the additional conditions for the dept_code and status_id. So the join will attempt to find matching records in the table.

Then add a WHERE so that you only include rows when the second JOIN didn't find anything.

[mongoose00318]

Like this? Though the results it's giving me are still not as expected.

SELECT 
    pd.*,
    ps.*
FROM `production_data`AS pd
LEFT JOIN production_status AS ps ON ps.order_id = pd.id
LEFT JOIN production_status ON ps.dept_code = 13 AND ps.status_id = 3
WHERE NOT EXISTS (SELECT * FROM production_status
				  WHERE ps.dept_code = 13 AND ps.status_id = 3)

Also, what do you mean when you said:

30 minutes ago, requinix said:

I don't know if you want that JOIN to be LEFT or not.

I'd like to mention, once I get the query working, I won't be selecting all fields. I remember the advice you all have provided with that. I only do it like this to keep the query shorter for the forum post.

Edited June 8, 2020 by mongoose00318

[requinix]

1. The second JOIN looks just like the first one, has all the same conditions, plus it looks for dept_core and status_id. The whole point is to find "matching" (whatever that means to you) production_status that also meet your exclusion criteria.
2. Forget the WHERE NOT EXISTS. Get rid of it. Instead you need a WHERE to make sure that the second production_status, which you should give an alias, did not match - the easiest method for that being to test that a non-nullable column was null.

[mongoose00318]

SELECT
    pd.*,
    ps.*
FROM
    `production_data` AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
JOIN production_status AS chk ON ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3
WHERE chk.order_id = NULL

Like this? Yea I was thinking about checking if a column as NULL! Just couldn't figure it out. 

[mongoose00318]

Hmm...must have screwed something up...Showing rows 0 - 24 (1958329 total, Query took 0.0064 seconds.)

That's a bit off...

Joins are super confusing. 

Edited June 8, 2020 by mongoose00318

[requinix]

1. Look at the second JOIN. Does the condition have anything to do with the table it's trying to pull in?
2. You cannot compare anything to NULL. The result will always be NULL. That's how it works. If you want to know whether something is null then you should be using IS NULL.

[mongoose00318]

Wait! I got something right! I realized I was referencing the first joins alias...

SELECT
    pd.*,
    ps.*
FROM
    `production_data` AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
JOIN production_status AS chk ON chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3

This is returning 415 matches! But when I do...

SELECT
    pd.*,
    ps.*
FROM
    `production_data` AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
JOIN production_status AS chk ON chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3
WHERE chk.id = NULL

I get zero matches.

[mongoose00318]

SELECT
    pd.*,
    ps.*
FROM
    `production_data` AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
JOIN production_status AS chk ON chk.order_id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3
WHERE chk.id IS NULL

Changed to that and still get 0 matches 😕

[requinix]

Look at the condition again. Are you using the right columns for everything?

[mongoose00318]

I wish there was some way to better visualize this. When I have it do the "explain SQL" it says I have an impossible where. I think all the columns are right. My head hurts though lol.

Edited June 8, 2020 by mongoose00318

[mongoose00318]

SELECT
    pd.*,
    ps.*
FROM
    production_data AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
JOIN production_status AS chk
ON
    chk.order_id = pd.id AND chk.dept_code = 13 AND chk.status_id = 3

Okay I think I found something wrong with the conditions. I was doing ps.id. But, still I think I'm doing something wrong: https://imgur.com/dUblOQX

Why would I be getting any other dept_codes returning other than 13 right now? Without the WHERE shouldn't everything have a dept_code of 13?

[mongoose00318]

I need it to give the exact opposite results of what this does: 

SELECT
    pd.*,
    ps.*
FROM
    production_data AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id AND ps.dept_code = 13 AND ps.status_id = 3

I've been tinkering with the 2nd JOIN but I just keep getting the same results...impossible where and dept_codes being returned which shouldn't be.

[requinix]

23 minutes ago, mongoose00318 said:

Without the WHERE shouldn't everything have a dept_code of 13?

No. Only the pd+chk pair check for the dept_code. But you are not looking at the pd+chk data. You are looking at the pd+ps data.

Put the WHERE back in and make the chk join a LEFT JOIN.

[mongoose00318]

Okay, it's working with it like this:

SELECT
    pd.*,
    ps.*
FROM
    production_data AS pd
JOIN production_status AS ps
ON
    ps.order_id = pd.id
LEFT JOIN production_status AS chk ON chk.id = ps.id AND chk.dept_code = 13 AND chk.status_id = 3
WHERE chk.id IS NULL

But, I've realized some other problems. For example, the field status_id has 4 possible values:

• 0 - Not started
• 1 - In progress
• 2 - Delayed
• 3 - Finished

So I'm getting records back with status_ids of 0, 1, & 2...which makes sense to me now. I guess I didn't think through the exclusion criteria thoroughly enough...if the order has a status_id of 3 I don't want it to return any other records from the production_status table. I hope I've provided enough data for that to be helpful.

Nonetheless, the query is now working and I think I understand what it is doing...mostly.

[requinix]

So I guess what you're saying is that the dept_code is irrelevant?

[mongoose00318]

No it's relevant. The dept_code of 13 represents the shipping dept. 

So here is an example:

Order 1794 had two status updates on 6/8/2020. The first one with a status_id of 1 means it was started. The next submission @ 07:22:50 with a status_id of 3 means the order has shipped. So, since that record (TBL: production_status; id: 4678; status_id: 3) exists, I'm unconcerned with any of the other records for that order. In this case record 4677.

Does that make sense? 

[mongoose00318]

Shipping is the last department a product goes through. If orders show as shipped (like row 4678 above) I don't want them to be included in the results from the query

[requinix]

If you don't care about other records once there's a status_id of 3 then why does the department matter?

[kicken]

Instead of the LEFT JOIN/NULL check, create a query to pull the order_id of all the rows with codes 13/3.  Use that as a sub-query in your main query with a NOT IN condition in the where clause.

select *
from blah
where
    order_id not in (select order_id from status where ...)