Post reply

double JOIN statement

  • November 21, 2007 at 4:56 am

    #184326

    Hiya,

    Just wondering if my double JOIN statement is correct. The scenario:Having 3 tables called FileData(FileDataUID-PK,FileUID), File(FileUID-PK,FileName) and SupportingInformation(SupportingUID-PK,FileUID,SupportingTypeUID), in the paranthesis the columns and indexes which is the primary key-the layout is just for better understanding.

    Now I want to delete all the records from FileData table for the SupportingUID from SupportingInformation table. I am trying this way:

    DELETE FROM FileData JOIN (SELECT FileUID FROM File JOIN SupportingInformation ON File.FileUID = SupportingInformation.FileUID WHERE SupportingInformation.SupportingUID = @SupportingUID AS JoinedFile) ON FileData.FileUID = JoinedFile

    Will this work for me?Will appreciate any help.

    Thanks

    Ben

  • November 21, 2007 at 5:36 am

    #753052

    The query will not work as the syntax is somewhat different when using joins...

    DELETE FD FROM FileData FD INNER JOIN SupportingInformation SI ON FD.FileUID = SI.FileUID AND SI.SupportingUID = @SupportingUID

    --Ramesh

  • November 21, 2007 at 5:52 am

    #753055

    Hi Ramesh,

    Your point is good, I know it will work as I tried before, but I wanted somehow and sctictly to go through the File table:the reason I wanted to use the Prinary Key FileUID and not the Foreign Key as a reference UID. Can the way, I thought before and is the same as yours Ramesh, cause any difference to compare to what I am trying to achieve?

    Thanks

    ben

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

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