Problem with SubQuery and 'IN' clause

Question

Problem with SubQuery and 'IN' clause

JamieReid

SSC Rookie

Points: 26
More actions
December 12, 2003 at 6:47 am

#107549

I've had a problem with a stored procedure which has been working without a problem for 14 months. It stopped functioning about a month ago. I cannot see anything significant in the events log of the server that may have caused a change. The following code was the cause of the problem:
SELECT X.*
FROM ACCOM_LIVE X
WHERE X.UNIQUEBOOKREF IN
(
SELECT A.UNIQUEBOOKREF
FROM ACCOM_LIVE A
GROUP BY UNIQUEBOOKREF,ACCOM_ORDER
HAVING COUNT(ACCOMLIVEID) >1
AND COUNT(DISTINCT(BOOKINGDATE))>1)
The query returned no records, yet if the results in the subquery were inserted into a temp table, and then the inner query changed to select data from this temp table - then the query returned the results that it should have.
Additionally if the outer query was changed to 'NOT IN' the script worked fine, and returned the expected records.
I have since created an exact copy of the 'ACCOM_LIVE' table with identical structure, indexes and records - and the original query works fine.
Has anyone got any ideas as to what has caused this - and how it can be avoided in the future. The problem is 'fixed' but I don't like a situation where I don't understand the cause.
Any suggestions much appreciated.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

gljjr SSChampion Points: 10080 More actions · Answer 1

I've never seen that. But just out of curiosity what happens if you change the query to the following?

 

SELECT X.*

FROM ACCOM_LIVE X 

    JOIN (SELECT UNIQUEBOOKREF

          FROM ACCOM_LIVE

          GROUP BY UNIQUEBOOKREF,ACCOM_ORDER

          HAVING COUNT(ACCOMLIVEID) >1

              AND COUNT(DISTINCT(BOOKINGDATE))>1 ) AS  A

ON X.UNIQUEBOOKREF = A.UNIQUEBOOKREF

Also, I'm hoping that you don't really use the SELECT X.* but actually explicitly use the field names instead!

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.