Loading ...
Sorry, an error occurred while loading the content.
 

RE: [SQLQueriesNoCode] Sub Queries

Expand Messages
  • Arnie Rowland
    Try something like this... (ATTENTION: For a more thorough and tested response, please include both TABLE CREATE code and data INSERT code, including sample
    Message 1 of 8 , Apr 29, 2006
      Try something like this...

      (ATTENTION: For a more thorough and tested response, please include both
      TABLE CREATE code and data INSERT code, including sample data in your
      request.)


      SELECT
      r.ID
      , r.RaftID
      , ( cp.Path + '\' + p.Path ) AS 'Path'
      , fe.CanScript
      , fe.IsActive
      , fe.DeleteOriginal
      , fn.FileName
      , tfn.FileName
      FROM RaftID r
      JOIN FileEvent fe
      ON r.RaftID = fe.RaftID_ID
      JOIN Path p
      ON p.ID = fe.PathID
      JOIN Path cp
      ON cp.ID = fe.CommonPathID
      JOIN FileName fn
      ON fn.ID = fe.FileNameID
      JOIN FileName tfn
      ON tfn.ID = TriggerID

      {Perhaps a GROUP BY, and/or WHERE clause also}

      - Arnie Rowland

      "I am a great believer in luck, and I find that the harder I work, the
      more I have of it." - Thomas Jefferson (1743-1826)


      -----Original Message-----
      From: SQLQueriesNoCode@yahoogroups.com
      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Falls, Travis D
      (HTSC, CASD)
      Sent: Thursday, April 27, 2006 9:46 AM
      To: Arnie
      Subject: [SQLQueriesNoCode] Sub Queries

      So I am banging my head against a wall with a correlated sub query
      issue; basically I have never had to write a query like this and I am
      stuck.

      I have 4 tables:

      RAFTID
      ID int 4
      RAFTID char 10

      FileName
      ID int 4
      FileName varchar 50

      Path
      ID int 4
      Path varchar 100

      FileEvent
      ID int 4
      CommonPathID int 4
      PathID int 4
      CanScript bit 1
      IsActive bit 1
      DeleteOriginal bit 1
      FileNameID int 4
      TriggerID int 4
      RAFTID_ID int 4


      I want to select all RAFTIDs and return the actual information back that
      I am storing key of in my FileEvent table. For example I want to return

      ID RAFTID Path (which would be CommonPath and Path concatenated)
      CanScript IsActive DeleteOriginal FileName
      TriggerFileName

      I have no idea how to get the CommonPath and Path and concatenate them
      based on the IDs in the FileEvent table, nor do I know how to get the
      TriggerFileName or the FileName based on the IDs. I can do this with a
      few queries and do the work in my code but... I would rather do it in a
      stored proc on my SQL Server. Any suggestions on how to tackle this
      would be greatly appreciated. Thanks.


      Travis D. Falls | Consultant | RAFT.Net | 860.547.4070 |
      Travis.Falls@...



      ************************************************************************
      *
      This communication, including attachments, is
      for the exclusive use of addressee and may contain proprietary,
      confidential and/or privileged information. If you are not the intended
      recipient, any use, copying, disclosure, dissemination or distribution
      is
      strictly prohibited. If you are not the intended recipient, please
      notify
      the sender immediately by return e-mail, delete this communication and
      destroy all copies.
      ************************************************************************
      *



      [Non-text portions of this message have been removed]




      Yahoo! Groups Links














      Yahoo! Groups Links
    Your message has been successfully submitted and would be delivered to recipients shortly.