Sudden problem with query which has been working fine for years

[mongoose00318]

Good Morning All!

I have a query that has been running fine for years and last Friday it started acting up and returning 0 results for some reason when it shouldn't be. The query is below:

$query = "
				SELECT
					lr.*,
					l.make,
					l.part_number,
					l.description,
					pd.job_number,
					pd.as400_ship_date,
					pd.line_item,
					pd.enterprise,
					pd.description AS order_description,
					pd.qty AS order_qty,
					log.name AS user_full_name
				FROM
					leds_requests lr
				LEFT JOIN
					leds l
				ON
					l.id = lr.product_id
				LEFT JOIN
					production_data pd
				ON
					pd.id = lr.order_id
				LEFT JOIN
					login log
				ON
					log.user_id = lr.user_id
				WHERE
					lr.id IN(
					SELECT
						MAX(id)
					FROM
						leds_requests
					WHERE
						status_id = 1
					GROUP BY
						order_id,
						product_id
				) AND lr.id NOT IN(
						SELECT
					        ref_request_id
					    FROM
					        leds_requests
					    WHERE
					        status_id IN(2,3,4)
				)
				GROUP BY
					order_id,
					product_id,
					job_number
	";

 

I think the problem is right here:

) AND lr.id NOT IN(
	SELECT
		ref_request_id
	FROM
		leds_requests
	WHERE
        status_id IN(2,3,4)
)

 

It's like it doesn't like how I am referencing lr.id in that line. Was there some update that could have caused this? I don't understand why it was working fine for so long and I haven't changed anything.

All of your help is much appreciated! I can provide table structures, etc. if need be. 

[mongoose00318]

Okay I fixed it. Here I just changed that sub query to look at a different column.

				SELECT
					lr.*,
					l.make,
					l.part_number,
					l.description,
					pd.job_number,
					pd.as400_ship_date,
					pd.line_item,
					pd.enterprise,
					pd.description AS order_description,
					pd.qty AS order_qty,
					log.name AS user_full_name
				FROM
					leds_requests lr
				LEFT JOIN
					leds l
				ON
					l.id = lr.product_id
				LEFT JOIN
					production_data pd
				ON
					pd.id = lr.order_id
				LEFT JOIN
					login log
				ON
					log.user_id = lr.user_id
				WHERE
					lr.id IN(
					SELECT
						MAX(id)
					FROM
						leds_requests
					WHERE
						status_id = 1
					GROUP BY
						order_id,
						product_id
				) AND lr.order_id NOT IN(
						SELECT
					        order_id
					    FROM
					        leds_requests
					    WHERE
					        status_id IN(2,3,4)
				)
				GROUP BY
					order_id,
					product_id,
					job_number

 

[Barand]

If that now is how the query should be, then it has apparently been producing the wrong results for years and not working "fine" as you thought.

[mongoose00318]

Lol yea that's possible. But, it did seem to be working how it was intended. 

[Barand]

Of course, just because a query returns no results it doesn't mean it failed - just that there were no results meeting the specified criteria at the time it was run.

Indeed, no results might be the best result. Consider

SELECT cust.name
     , comp.comment
FROM customer cust
     JOIN 
     british_railways_complaints_received comp USING (customer_id)
WHERE complaint_date = ?

The desired, but highly improbable, result would be none.

Conversely, a query that returns results hasn't ncessarily worked as was intended in its specification.