Cartesian product

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Cartesian product

Cartesian product

Rate Topic

Display Mode

Topic Options

Author

Message

itlk

Posted Wednesday, October 24, 2012 10:54 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:21 AM
Points: 10, Visits: 111

Hi,

I am wondering if the following query is correct. I have tested it on a small record set, and I get the correct result, but I am worry if the query could lead to unwanted Cartesian product:

declare @wksInPse as table
(
[WorkstationId] [int] ,
[WorkstationGuid] [uniqueidentifier]
)

delete from tbl_Workstations
where WorkstationGuid in (
select WorkstationGuid
from @wksInPse pse
where tbl_Workstations.WorkstationId <> pse.WorkstationId );

The column WorkstationId in the table wksInPse is populated from another fixed table, where this column is a Primary Key.
The column WorkstationId in the table tbl_Workstations is a Primary Key column. Does the clause WHERE need to be extended to look like:

WHERE tbl_Workstations.WorkstationGuid = pse.WorkstationGuid
AND tbl_Workstations.WorkstationId <> pse.WorkstationId

Thanks for ideas.

Lubomir

Post #1376593

Jason Selburg

Posted Wednesday, October 24, 2012 1:42 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:01 PM
Points: 2,582, Visits: 3,555

What exactly are you trying to accomplish? Delete any workstation ID's that dont exist in your table?

If so ....

DELETE FROM W 
FROM tbl_Workstations AS W
    LEFT JOIN @wksInPse AS pse
        ON W.WorkstationId = pse.WorkstationId
WHERE pse.WorkstationID IS NULL

If not, then we need more info.

Future note, read the article in my signature, and you'll get more timely and accurate responses.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #1376649

itlk

Posted Wednesday, October 24, 2012 3:56 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:21 AM
Points: 10, Visits: 111

It is expected to delete workstations from the first table, which have the same Guid but different Id in the second table.

The script is an extract from the bigger script. I just want to confirm what this part of the SQL is really doing.
In the mantime I was able to analyse the execution plan and the predicate generated by the SQL, and it looks like that script is working properly, i.e. uses the where clause
with '=' condition for Guid and <> condition for ID, even the first condition is not explicitelly stated in the source script.

As far as an ugly formating in the posting: I apologize for that, but I still don't know how to embeded a nice formated SQL statement into the post. To make it even more confusing, the Web UI shows the post formated correctly during the posting, but the result, after posting, is unformated text.

Thank you for your time.

Post #1376703

« Prev Topic | Next Topic »

Permissions