SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cartesian product


Cartesian product

Author
Message
itlk
itlk
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 122
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
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 4110
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
itlk
itlk
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 122
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search