Jump to content

Sudden problem with query which has been working fine for years


mongoose00318

Recommended Posts

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. 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.