Stored procedure SQL issue

[E11esar]

Hi there.

Within my stored procedure I have a piece of SQL that is supposed to remove from a temporary table, any values that are not set to '1' for a particular field, but this does not work as required.

The SQL in question looks like this:

DELETE FROM table1
WHERE value_1 NOT IN
(
    SELECT tab1.value_1
    FROM table1 tab1
 
    JOIN table2 tab2 ON tab1.value_1 = tab2.value_1 AND tab1.line_no = tab2.line_no
    AND tab1.client = tab2.client
 
    JOIN table3 tab3 ON tab3.client = tab2.client 
    AND tab3.THIS_VALUE = 1 AND tab3.value_2 = tab2.value_2
 
    JOIN table4 tab4 ON tab3.client = tab4.client AND tab3.value_3 = tab4.tab4_value
 
    JOIN table5 tab5 ON tab5.client = tab4.client AND tab5.art_id = tab4.art_id
    AND tab5.Sub_id = @SubID AND tab5.Seq_no = @SeqNo
    AND tab1.client = @Client
)
 

Can you see what I have done wrong here please, as the test for tab3.THIS_VALUE = 1 is returbning results for THIS_VALUE = 0 also.

Thank you.

[ck9663]

Before we proceed, are you sure the temp table is accessible by the above mentioned query?

~~ CK

[E11esar]

Hi there.

Yes the table is accessible and is created earlier on within the stored procedure.

Thank you.

[ck9663]

And when you run your sub-query, there are results that return?

~~ CK

[NeoPa]

E11esar, it seems your description of your problem doesn't match the SQL that you've posted. The question implies the value should 1 to be deleted. Furthermore, if you need telling that the data returned from your subquery will be necessary to help resolve this then here we go. It will be. We don't know what's in your tables, or even how they're structured. It will be necessary to post the data at least.

Please take more care in future to post a full and clear question in your first post. I'd have thought you'd been with us long enough to understand that by now.