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

BEGINNER NEEDS HELP

Expand Messages
  • CharlesMarkCarroll
    From: lv_solutions Date: Fri Apr 2, 2010 3:29 pm Subject: BEGINNER NEEDS HELP Need help please with following Query Table 1 Holds user
    Message 1 of 18 , Apr 2, 2010
    • 0 Attachment
      From: "lv_solutions" <lvs.mail@...>
      Date: Fri Apr 2, 2010 3:29 pm
      Subject: BEGINNER NEEDS HELP


      Need help please with following Query

      Table 1 Holds user info
      Table Name = USERS: Fields are: UserID, UserName, Full Name

      Table2 holds permissions for users to view cameras
      Table Name = Permissions: Fields are: PermissionID, UserID, CameraID,
      AllowViewing

      Table3 - has list of cameras and if they are active
      TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,

      Table 4 holds Camera Name
      Table Name = SYS_Location: Fields Are: LocationID, CameraLocation

      First off It is not my database so I cannot change the tables in any manner.

      I have written the following query to display a list of cameras that the users
      have.

      The problem I have is that if I add a camera to the Camera Table it does not
      show up in the list.

      Can someone help.

      SELECT Users.UserId, Users.UserName, Users.FullName, Permissions.CameraID,
      Permissions.AllowViewing, CM_Cameras.Active,
      SYS_Locations.Location
      FROM Permissions INNER JOIN
      Users ON Permissions.UserID = Users.UserId INNER JOIN
      CM_Cameras ON Permissions.CameraID = CM_Cameras.CameraID
      INNER JOIN
      SYS_Locations ON CM_Cameras.LocationID =
      SYS_Locations.LocationID
      WHERE Users.UserID = :aUserID
      ORDER BY Users.UserId
    • charlesmarkcarroll
      From: lv_solutions Date: Fri Apr 2, 2010 3:29 pm Subject: BEGINNER NEEDS HELP Need help please with following Query Table 1 Holds user
      Message 2 of 18 , Apr 2, 2010
      • 0 Attachment
        From: "lv_solutions" <lvs.mail@...>
        Date: Fri Apr 2, 2010 3:29 pm
        Subject: BEGINNER NEEDS HELP


        Need help please with following Query

        Table 1 Holds user info
        Table Name = USERS: Fields are: UserID, UserName, Full Name

        Table2 holds permissions for users to view cameras
        Table Name = Permissions: Fields are: PermissionID, UserID, CameraID,
        AllowViewing

        Table3 - has list of cameras and if they are active
        TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,

        Table 4 holds Camera Name
        Table Name = SYS_Location: Fields Are: LocationID, CameraLocation

        First off It is not my database so I cannot change the tables in any manner.

        I have written the following query to display a list of cameras that the users
        have.

        The problem I have is that if I add a camera to the Camera Table it does not
        show up in the list.

        Can someone help.

        SELECT Users.UserId, Users.UserName, Users.FullName, Permissions.CameraID,
        Permissions.AllowViewing, CM_Cameras.Active,
        SYS_Locations.Location
        FROM Permissions INNER JOIN
        Users ON Permissions.UserID = Users.UserId INNER JOIN
        CM_Cameras ON Permissions.CameraID = CM_Cameras.CameraID
        INNER JOIN
        SYS_Locations ON CM_Cameras.LocationID =
        SYS_Locations.LocationID
        WHERE Users.UserID = :aUserID
        ORDER BY Users.UserId
      • John Warner
        If you add a camera it logically would not show up in a list of users and view permissions as you have not assigned a user nor permission to the camera. To
        Message 3 of 18 , Apr 3, 2010
        • 0 Attachment
          If you add a camera it logically would not show up in a list of users and
          view permissions as you have not assigned a user nor permission to the
          camera. To list cameras that lack this assignment, and OUTER JOIN of the
          camera table should produce the desired result.

          This all assumes (you know about assumptions) that I have understood the
          problem correctly.

          SELECT
          u.UserId,
          u.UserName,
          u.FullName,
          p.CameraID,
          p.AllowViewing,
          c.Active,
          s.Location
          FROM
          Permissions p
          INNER JOIN Users u
          ON p.UserID = u.UserId
          LEFT JOIN CM_Cameras c
          ON p.CameraID = c.CameraID
          INNER JOIN SYS_Locations s
          ON c.LocationID = s.LocationID

          WHERE u.UserID = :aUserID (<-???)

          ORDER BY u.UserId

          Note without the actual tables to test, it might be LEFT should be
          replaced by RIGHT I always get this 'wrong' on my first try. Also to test,
          drop the WHERE clause just to see if this gets the camera. Since the 'new'
          camera would not match a UserID the where might filter out the new camera.

          John Warner


          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
          > CharlesMarkCarroll
          > Sent: Friday, April 02, 2010 8:00 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] BEGINNER NEEDS HELP
          >
          > From: "lv_solutions" <lvs.mail@...>
          > Date: Fri Apr 2, 2010 3:29 pm
          > Subject: BEGINNER NEEDS HELP
          >
          >
          > Need help please with following Query
          >
          > Table 1 Holds user info
          > Table Name = USERS: Fields are: UserID, UserName, Full Name
          >
          > Table2 holds permissions for users to view cameras
          > Table Name = Permissions: Fields are: PermissionID, UserID, CameraID,
          > AllowViewing
          >
          > Table3 - has list of cameras and if they are active
          > TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,
          >
          > Table 4 holds Camera Name
          > Table Name = SYS_Location: Fields Are: LocationID, CameraLocation
          >
          > First off It is not my database so I cannot change the tables in any
          manner.
          >
          > I have written the following query to display a list of cameras that the
          users
          > have.
          >
          > The problem I have is that if I add a camera to the Camera Table it
          does not
          > show up in the list.
          >
          > Can someone help.
          >
          > SELECT Users.UserId, Users.UserName, Users.FullName,
          > Permissions.CameraID,
          > Permissions.AllowViewing, CM_Cameras.Active,
          > SYS_Locations.Location
          > FROM Permissions INNER JOIN
          > Users ON Permissions.UserID = Users.UserId INNER
          JOIN
          > CM_Cameras ON Permissions.CameraID =
          > CM_Cameras.CameraID
          > INNER JOIN
          > SYS_Locations ON CM_Cameras.LocationID =
          > SYS_Locations.LocationID
          > WHERE Users.UserID = :aUserID
          > ORDER BY Users.UserId
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
        • lv_solutions
          It seems as though you fully understand my issue, and thanks for the reply. I left off the where, I tried both LEFT and RIGHT but the newly added cameras that
          Message 4 of 18 , Apr 3, 2010
          • 0 Attachment
            It seems as though you fully understand my issue, and thanks for the reply.

            I left off the where, I tried both LEFT and RIGHT but the newly added cameras that the users do not have assigned to them and do not have permission assigned are not showing up for the users.



            --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...> wrote:
            >
            > If you add a camera it logically would not show up in a list of users and
            > view permissions as you have not assigned a user nor permission to the
            > camera. To list cameras that lack this assignment, and OUTER JOIN of the
            > camera table should produce the desired result.
            >
            > This all assumes (you know about assumptions) that I have understood the
            > problem correctly.
            >
            > SELECT
            > u.UserId,
            > u.UserName,
            > u.FullName,
            > p.CameraID,
            > p.AllowViewing,
            > c.Active,
            > s.Location
            > FROM
            > Permissions p
            > INNER JOIN Users u
            > ON p.UserID = u.UserId
            > LEFT JOIN CM_Cameras c
            > ON p.CameraID = c.CameraID
            > INNER JOIN SYS_Locations s
            > ON c.LocationID = s.LocationID
            >
            > WHERE u.UserID = :aUserID (<-???)
            >
            > ORDER BY u.UserId
            >
            > Note without the actual tables to test, it might be LEFT should be
            > replaced by RIGHT I always get this 'wrong' on my first try. Also to test,
            > drop the WHERE clause just to see if this gets the camera. Since the 'new'
            > camera would not match a UserID the where might filter out the new camera.
            >
            > John Warner
            >
            >
            > > -----Original Message-----
            > > From: SQLQueriesNoCode@yahoogroups.com
            > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
            > > CharlesMarkCarroll
            > > Sent: Friday, April 02, 2010 8:00 PM
            > > To: SQLQueriesNoCode@yahoogroups.com
            > > Subject: [SQLQueriesNoCode] BEGINNER NEEDS HELP
            > >
            > > From: "lv_solutions" <lvs.mail@...>
            > > Date: Fri Apr 2, 2010 3:29 pm
            > > Subject: BEGINNER NEEDS HELP
            > >
            > >
            > > Need help please with following Query
            > >
            > > Table 1 Holds user info
            > > Table Name = USERS: Fields are: UserID, UserName, Full Name
            > >
            > > Table2 holds permissions for users to view cameras
            > > Table Name = Permissions: Fields are: PermissionID, UserID, CameraID,
            > > AllowViewing
            > >
            > > Table3 - has list of cameras and if they are active
            > > TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,
            > >
            > > Table 4 holds Camera Name
            > > Table Name = SYS_Location: Fields Are: LocationID, CameraLocation
            > >
            > > First off It is not my database so I cannot change the tables in any
            > manner.
            > >
            > > I have written the following query to display a list of cameras that the
            > users
            > > have.
            > >
            > > The problem I have is that if I add a camera to the Camera Table it
            > does not
            > > show up in the list.
            > >
            > > Can someone help.
            > >
            > > SELECT Users.UserId, Users.UserName, Users.FullName,
            > > Permissions.CameraID,
            > > Permissions.AllowViewing, CM_Cameras.Active,
            > > SYS_Locations.Location
            > > FROM Permissions INNER JOIN
            > > Users ON Permissions.UserID = Users.UserId INNER
            > JOIN
            > > CM_Cameras ON Permissions.CameraID =
            > > CM_Cameras.CameraID
            > > INNER JOIN
            > > SYS_Locations ON CM_Cameras.LocationID =
            > > SYS_Locations.LocationID
            > > WHERE Users.UserID = :aUserID
            > > ORDER BY Users.UserId
            > >
            > >
            > >
            > > ------------------------------------
            > >
            > > Yahoo! Groups Links
            > >
            > >
            > >
            >
          • John Warner
            SQL Server 2005 Express I need to UPDATE 3 fields in a table let s call it Master. The data is gotten from a SELECT which fetches all 3 of the needed fields
            Message 5 of 18 , Apr 5, 2010
            • 0 Attachment
              SQL Server 2005 Express

              I need to UPDATE 3 fields in a table let's call it Master.
              The data is gotten from a SELECT which fetches all 3 of the needed fields
              and quite a few rows.

              Can I do something like

              UPDATE Master
              SET field1, field2, field3

              (SELECT field1, field2, field3
              FROM tables, join ....
              WHERE prime key table1 = table2 etc) SEL

              WHERE primeKey Master = PrimeKey SEL

              Am I being clear here because the syntax is obviously not right but I hope
              shows what I'm after.

              The rows in Master will exist and the key will exist, I just need to put
              data in three fields in Master gathered from a nested query.

              Also the nested query has nested queries, each field is pulled from a
              different table and the data is pivoted so the above nest does not really
              reflect what is happening. If desired I can post just trying to save a bit
              of space.


              Thanks for the lesson I hope I'm about to receive!

              John Warner
            • Tim Mitchell
              John, You can t do an UPDATE... SELECT, but you can do an UPDATE with an INNER JOIN to get the desired result. Something like the following should work:
              Message 6 of 18 , Apr 5, 2010
              • 0 Attachment
                John,

                You can't do an UPDATE... SELECT, but you can do an UPDATE with an INNER
                JOIN to get the desired result. Something like the following should work:

                UPDATE m
                SET m.field1 = t1.field1
                , m.field2 = t2.field2
                , m.field3 = t3.field3
                FROM Master m
                INNER JOIN Table1 t1 ON <join conditions>
                INNER JOIN Table2 t2 ON <join conditions>
                INNER JOIN Table3 t3 ON <join conditions>

                Let me know if this doesn't answer your question....
                --
                Tim Mitchell, MCTS
                SQL Server MVP
                Blog: http://www.TimMitchell.net
                Twitter: http://twitter.com/Tim_Mitchell


                On Mon, Apr 5, 2010 at 2:04 PM, John Warner <john@...> wrote:

                >
                >
                > SQL Server 2005 Express
                >
                > I need to UPDATE 3 fields in a table let's call it Master.
                > The data is gotten from a SELECT which fetches all 3 of the needed fields
                > and quite a few rows.
                >
                > Can I do something like
                >
                > UPDATE Master
                > SET field1, field2, field3
                >
                > (SELECT field1, field2, field3
                > FROM tables, join ....
                > WHERE prime key table1 = table2 etc) SEL
                >
                > WHERE primeKey Master = PrimeKey SEL
                >
                > Am I being clear here because the syntax is obviously not right but I hope
                > shows what I'm after.
                >
                > The rows in Master will exist and the key will exist, I just need to put
                > data in three fields in Master gathered from a nested query.
                >
                > Also the nested query has nested queries, each field is pulled from a
                > different table and the data is pivoted so the above nest does not really
                > reflect what is happening. If desired I can post just trying to save a bit
                > of space.
                >
                > Thanks for the lesson I hope I'm about to receive!
                >
                > John Warner
                >
                >
                >


                [Non-text portions of this message have been removed]
              • Noman Aftab
                UPDATE MASTER SET FIELD1 = SEL.FIELD1, FIELD2 = SEL.FIELD2, FIELD3 = SEL.FIELD3 FROM MASTER INNER JOIN SEL ON SEL.PRIMARY_KEY = MASTER.PRIMARY_KEY you can
                Message 7 of 18 , Apr 5, 2010
                • 0 Attachment
                  UPDATE MASTER
                  SET FIELD1 = SEL.FIELD1, FIELD2 = SEL.FIELD2, FIELD3 = SEL.FIELD3
                  FROM MASTER
                  INNER JOIN SEL ON SEL.PRIMARY_KEY = MASTER.PRIMARY_KEY

                  you can join other tables in this query or simple use a subquery alias of which will be "SEL".

                  UPDATE MASTER
                  SET FIELD1 = SEL.FIELD1, FIELD2 = SEL.FIELD2, FIELD3 = SEL.FIELD3
                  FROM MASTER
                  INNER JOIN (select primary_key, field1, field2, field3 from someTable inner join anotherTable where 1=1) SEL ON SEL.PRIMARY_KEY = MASTER.PRIMARY_KEY



                  Best Wishes,
                  Noman Aftab


                  http://www.corpus.quran.com/wordbyword.jsp
                  http://www.jalandhari.qsh.eu





                  ________________________________
                  From: John Warner <john@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Tue, 6 April, 2010 12:04:06 AM
                  Subject: [SQLQueriesNoCode] RE: UPDATE question


                  SQL Server 2005 Express

                  I need to UPDATE 3 fields in a table let's call it Master.
                  The data is gotten from a SELECT which fetches all 3 of the needed fields
                  and quite a few rows.

                  Can I do something like

                  UPDATE Master
                  SET field1, field2, field3

                  (SELECT field1, field2, field3
                  FROM tables, join ....
                  WHERE prime key table1 = table2 etc) SEL

                  WHERE primeKey Master = PrimeKey SEL

                  Am I being clear here because the syntax is obviously not right but I hope
                  shows what I'm after.

                  The rows in Master will exist and the key will exist, I just need to put
                  data in three fields in Master gathered from a nested query.

                  Also the nested query has nested queries, each field is pulled from a
                  different table and the data is pivoted so the above nest does not really
                  reflect what is happening. If desired I can post just trying to save a bit
                  of space.

                  Thanks for the lesson I hope I'm about to receive!

                  John Warner





                  The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
                • John Warner
                  Thanks I think you may have come real close to actually writing the query. Can t play with it right now, but you ll see me holler tomorrow if this fails (might
                  Message 8 of 18 , Apr 5, 2010
                  • 0 Attachment
                    Thanks I think you may have come real close to actually writing the query. Can't play with it right now, but you'll see me holler tomorrow if this fails (might even hear me) }:-)

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                    > Sent: Monday, April 05, 2010 3:22 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: Re: [SQLQueriesNoCode] RE: UPDATE question
                    >
                    >
                    >
                    > UPDATE MASTER
                    > SET FIELD1 = SEL.FIELD1, FIELD2 = SEL.FIELD2, FIELD3 = SEL.FIELD3
                    > FROM MASTER
                    > INNER JOIN SEL ON SEL.PRIMARY_KEY = MASTER.PRIMARY_KEY
                    >
                    > you can join other tables in this query or simple use a subquery alias of which
                    > will be "SEL".
                    >
                    > UPDATE MASTER
                    > SET FIELD1 = SEL.FIELD1, FIELD2 = SEL.FIELD2, FIELD3 = SEL.FIELD3
                    > FROM MASTER
                    > INNER JOIN (select primary_key, field1, field2, field3 from someTable inner
                    > join anotherTable where 1=1) SEL ON SEL.PRIMARY_KEY =
                    > MASTER.PRIMARY_KEY
                    >
                    >
                    >
                    > Best Wishes,
                    > Noman Aftab
                    >
                    >
                    > http://www.corpus.quran.com/wordbyword.jsp
                    > http://www.jalandhari.qsh.eu
                    >
                    >
                    >
                    >
                    >
                    > ________________________________
                    > From: John Warner <john@...>
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Sent: Tue, 6 April, 2010 12:04:06 AM
                    > Subject: [SQLQueriesNoCode] RE: UPDATE question
                    >
                    >
                    > SQL Server 2005 Express
                    >
                    > I need to UPDATE 3 fields in a table let's call it Master.
                    > The data is gotten from a SELECT which fetches all 3 of the needed fields
                    > and quite a few rows.
                    >
                    > Can I do something like
                    >
                    > UPDATE Master
                    > SET field1, field2, field3
                    >
                    > (SELECT field1, field2, field3
                    > FROM tables, join ....
                    > WHERE prime key table1 = table2 etc) SEL
                    >
                    > WHERE primeKey Master = PrimeKey SEL
                    >
                    > Am I being clear here because the syntax is obviously not right but I hope
                    > shows what I'm after.
                    >
                    > The rows in Master will exist and the key will exist, I just need to put
                    > data in three fields in Master gathered from a nested query.
                    >
                    > Also the nested query has nested queries, each field is pulled from a
                    > different table and the data is pivoted so the above nest does not really
                    > reflect what is happening. If desired I can post just trying to save a bit
                    > of space.
                    >
                    > Thanks for the lesson I hope I'm about to receive!
                    >
                    > John Warner
                    >
                    >
                    >
                    >
                    >
                    > The INTERNET now has a personality. YOURS! See your Yahoo!
                    > Homepage. http://in.yahoo.com/
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                  • John Warner
                    Thanks I can t play with it right now but that looks likely to be my solution. John Warner ... fields ... hope ... put ... really ... bit
                    Message 9 of 18 , Apr 5, 2010
                    • 0 Attachment
                      Thanks I can't play with it right now but that looks likely to be my
                      solution.

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Tim Mitchell
                      > Sent: Monday, April 05, 2010 3:15 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: Re: [SQLQueriesNoCode] RE: UPDATE question
                      >
                      > John,
                      >
                      > You can't do an UPDATE... SELECT, but you can do an UPDATE with an
                      > INNER
                      > JOIN to get the desired result. Something like the following should
                      work:
                      >
                      > UPDATE m
                      > SET m.field1 = t1.field1
                      > , m.field2 = t2.field2
                      > , m.field3 = t3.field3
                      > FROM Master m
                      > INNER JOIN Table1 t1 ON <join conditions>
                      > INNER JOIN Table2 t2 ON <join conditions>
                      > INNER JOIN Table3 t3 ON <join conditions>
                      >
                      > Let me know if this doesn't answer your question....
                      > --
                      > Tim Mitchell, MCTS
                      > SQL Server MVP
                      > Blog: http://www.TimMitchell.net
                      > Twitter: http://twitter.com/Tim_Mitchell
                      >
                      >
                      > On Mon, Apr 5, 2010 at 2:04 PM, John Warner <john@...> wrote:
                      >
                      > >
                      > >
                      > > SQL Server 2005 Express
                      > >
                      > > I need to UPDATE 3 fields in a table let's call it Master.
                      > > The data is gotten from a SELECT which fetches all 3 of the needed
                      fields
                      > > and quite a few rows.
                      > >
                      > > Can I do something like
                      > >
                      > > UPDATE Master
                      > > SET field1, field2, field3
                      > >
                      > > (SELECT field1, field2, field3
                      > > FROM tables, join ....
                      > > WHERE prime key table1 = table2 etc) SEL
                      > >
                      > > WHERE primeKey Master = PrimeKey SEL
                      > >
                      > > Am I being clear here because the syntax is obviously not right but I
                      hope
                      > > shows what I'm after.
                      > >
                      > > The rows in Master will exist and the key will exist, I just need to
                      put
                      > > data in three fields in Master gathered from a nested query.
                      > >
                      > > Also the nested query has nested queries, each field is pulled from a
                      > > different table and the data is pivoted so the above nest does not
                      really
                      > > reflect what is happening. If desired I can post just trying to save a
                      bit
                      > > of space.
                      > >
                      > > Thanks for the lesson I hope I'm about to receive!
                      > >
                      > > John Warner
                      > >
                      > >
                      > >
                      >
                      >
                      > [Non-text portions of this message have been removed]
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    • John Warner
                      Try replacing: p.CameraID with c.CameraID Question when you add a camera, what table(s) are you adding it to since 4 has the name and 3 appears to be the
                      Message 10 of 18 , Apr 6, 2010
                      • 0 Attachment
                        Try replacing:
                        p.CameraID

                        with
                        c.CameraID

                        Question when you add a camera, what table(s) are you adding it to since 4
                        has the name and 3 appears to be the camera table. Not clear where this
                        newly added data is to begin with.

                        John Warner


                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of lv_solutions
                        > Sent: Saturday, April 03, 2010 2:23 PM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP
                        >
                        > It seems as though you fully understand my issue, and thanks for the
                        reply.
                        >
                        > I left off the where, I tried both LEFT and RIGHT but the newly added
                        cameras
                        > that the users do not have assigned to them and do not have permission
                        > assigned are not showing up for the users.
                        >
                        >
                        >
                        > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@...>
                        > wrote:
                        > >
                        > > If you add a camera it logically would not show up in a list of users
                        and
                        > > view permissions as you have not assigned a user nor permission to the
                        > > camera. To list cameras that lack this assignment, and OUTER JOIN of
                        the
                        > > camera table should produce the desired result.
                        > >
                        > > This all assumes (you know about assumptions) that I have understood
                        the
                        > > problem correctly.
                        > >
                        > > SELECT
                        > > u.UserId,
                        > > u.UserName,
                        > > u.FullName,
                        > > p.CameraID,
                        > > p.AllowViewing,
                        > > c.Active,
                        > > s.Location
                        > > FROM
                        > > Permissions p
                        > > INNER JOIN Users u
                        > > ON p.UserID = u.UserId
                        > > LEFT JOIN CM_Cameras c
                        > > ON p.CameraID = c.CameraID
                        > > INNER JOIN SYS_Locations s
                        > > ON c.LocationID = s.LocationID
                        > >
                        > > WHERE u.UserID = :aUserID (<-???)
                        > >
                        > > ORDER BY u.UserId
                        > >
                        > > Note without the actual tables to test, it might be LEFT should be
                        > > replaced by RIGHT I always get this 'wrong' on my first try. Also to
                        test,
                        > > drop the WHERE clause just to see if this gets the camera. Since the
                        'new'
                        > > camera would not match a UserID the where might filter out the new
                        camera.
                        > >
                        > > John Warner
                        > >
                        > >
                        > > > -----Original Message-----
                        > > > From: SQLQueriesNoCode@yahoogroups.com
                        > > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                        > > > CharlesMarkCarroll
                        > > > Sent: Friday, April 02, 2010 8:00 PM
                        > > > To: SQLQueriesNoCode@yahoogroups.com
                        > > > Subject: [SQLQueriesNoCode] BEGINNER NEEDS HELP
                        > > >
                        > > > From: "lv_solutions" <lvs.mail@...>
                        > > > Date: Fri Apr 2, 2010 3:29 pm
                        > > > Subject: BEGINNER NEEDS HELP
                        > > >
                        > > >
                        > > > Need help please with following Query
                        > > >
                        > > > Table 1 Holds user info
                        > > > Table Name = USERS: Fields are: UserID, UserName, Full Name
                        > > >
                        > > > Table2 holds permissions for users to view cameras
                        > > > Table Name = Permissions: Fields are: PermissionID, UserID,
                        CameraID,
                        > > > AllowViewing
                        > > >
                        > > > Table3 - has list of cameras and if they are active
                        > > > TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,
                        > > >
                        > > > Table 4 holds Camera Name
                        > > > Table Name = SYS_Location: Fields Are: LocationID, CameraLocation
                        > > >
                        > > > First off It is not my database so I cannot change the tables in any
                        > > manner.
                        > > >
                        > > > I have written the following query to display a list of cameras that
                        the
                        > > users
                        > > > have.
                        > > >
                        > > > The problem I have is that if I add a camera to the Camera Table it
                        > > does not
                        > > > show up in the list.
                        > > >
                        > > > Can someone help.
                        > > >
                        > > > SELECT Users.UserId, Users.UserName, Users.FullName,
                        > > > Permissions.CameraID,
                        > > > Permissions.AllowViewing, CM_Cameras.Active,
                        > > > SYS_Locations.Location
                        > > > FROM Permissions INNER JOIN
                        > > > Users ON Permissions.UserID = Users.UserId
                        INNER
                        > > JOIN
                        > > > CM_Cameras ON Permissions.CameraID =
                        > > > CM_Cameras.CameraID
                        > > > INNER JOIN
                        > > > SYS_Locations ON CM_Cameras.LocationID =
                        > > > SYS_Locations.LocationID
                        > > > WHERE Users.UserID = :aUserID
                        > > > ORDER BY Users.UserId
                        > > >
                        > > >
                        > > >
                        > > > ------------------------------------
                        > > >
                        > > > Yahoo! Groups Links
                        > > >
                        > > >
                        > > >
                        > >
                        >
                        >
                        >
                        >
                        > ------------------------------------
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >
                      • John Warner
                        First thanks again to all for the suggestions on the UPDATE query yesterday. Works like a charm. But another small problem has popped up and I think this is in
                        Message 11 of 18 , Apr 6, 2010
                        • 0 Attachment
                          First thanks again to all for the suggestions on the UPDATE query
                          yesterday. Works like a charm. But another small problem has popped up and
                          I think this is in SQL Server.

                          One of the fields I hit with my subqueries in the UPDATE is a Name column
                          (vendors) and some of the names have the '&' sign in them such as
                          John & Warner, Inc

                          The SELECT:

                          SELECT Vendor_or_Mfg + ', ' as 'data()'
                          FROM [Table_4(Vendor_or_Mfg)] t4
                          WHERE t4.F_documentID=t.F_documentID
                          FOR XML PATH('')) Vendor_or_Mfg

                          I get John & Warner, Inc

                          I need the & but not the amp. I know the XML is doing this, anyway to make
                          it 'stop' or a simple way without a cursor to pass through the rows
                          returned and replace & with just &? Set preferred to procedural process
                          is what I'm after.

                          John Warner
                        • lvs mail
                          That does not work either. I get a multitude of Same# UserID with Each Carera =ID Sincerely Trevor Cell: (407)467-5435 ________________________________ From:
                          Message 12 of 18 , Apr 6, 2010
                          • 0 Attachment
                            That does not work either. I get a multitude of Same# UserID with Each
                            Carera =ID

                            Sincerely

                            Trevor

                            Cell: (407)467-5435

                            ________________________________

                            From: SQLQueriesNoCode@yahoogroups.com
                            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                            Sent: Tuesday, April 06, 2010 10:15 AM
                            To: SQLQueriesNoCode@yahoogroups.com
                            Subject: RE: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP





                            Try replacing:
                            p.CameraID

                            with
                            c.CameraID

                            Question when you add a camera, what table(s) are you adding it to since
                            4
                            has the name and 3 appears to be the camera table. Not clear where this
                            newly added data is to begin with.

                            John Warner

                            > -----Original Message-----
                            > From: SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com>
                            > [mailto:SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of lv_solutions
                            > Sent: Saturday, April 03, 2010 2:23 PM
                            > To: SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com>
                            > Subject: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP
                            >
                            > It seems as though you fully understand my issue, and thanks for the
                            reply.
                            >
                            > I left off the where, I tried both LEFT and RIGHT but the newly added
                            cameras
                            > that the users do not have assigned to them and do not have permission
                            > assigned are not showing up for the users.
                            >
                            >
                            >
                            > --- In SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com> , "John Warner" <john@...>
                            > wrote:
                            > >
                            > > If you add a camera it logically would not show up in a list of
                            users
                            and
                            > > view permissions as you have not assigned a user nor permission to
                            the
                            > > camera. To list cameras that lack this assignment, and OUTER JOIN of
                            the
                            > > camera table should produce the desired result.
                            > >
                            > > This all assumes (you know about assumptions) that I have understood
                            the
                            > > problem correctly.
                            > >
                            > > SELECT
                            > > u.UserId,
                            > > u.UserName,
                            > > u.FullName,
                            > > p.CameraID,
                            > > p.AllowViewing,
                            > > c.Active,
                            > > s.Location
                            > > FROM
                            > > Permissions p
                            > > INNER JOIN Users u
                            > > ON p.UserID = u.UserId
                            > > LEFT JOIN CM_Cameras c
                            > > ON p.CameraID = c.CameraID
                            > > INNER JOIN SYS_Locations s
                            > > ON c.LocationID = s.LocationID
                            > >
                            > > WHERE u.UserID = :aUserID (<-???)
                            > >
                            > > ORDER BY u.UserId
                            > >
                            > > Note without the actual tables to test, it might be LEFT should be
                            > > replaced by RIGHT I always get this 'wrong' on my first try. Also to
                            test,
                            > > drop the WHERE clause just to see if this gets the camera. Since the
                            'new'
                            > > camera would not match a UserID the where might filter out the new
                            camera.
                            > >
                            > > John Warner
                            > >
                            > >
                            > > > -----Original Message-----
                            > > > From: SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com>
                            > > > [mailto:SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of
                            > > > CharlesMarkCarroll
                            > > > Sent: Friday, April 02, 2010 8:00 PM
                            > > > To: SQLQueriesNoCode@yahoogroups.com
                            <mailto:SQLQueriesNoCode%40yahoogroups.com>
                            > > > Subject: [SQLQueriesNoCode] BEGINNER NEEDS HELP
                            > > >
                            > > > From: "lv_solutions" <lvs.mail@...>
                            > > > Date: Fri Apr 2, 2010 3:29 pm
                            > > > Subject: BEGINNER NEEDS HELP
                            > > >
                            > > >
                            > > > Need help please with following Query
                            > > >
                            > > > Table 1 Holds user info
                            > > > Table Name = USERS: Fields are: UserID, UserName, Full Name
                            > > >
                            > > > Table2 holds permissions for users to view cameras
                            > > > Table Name = Permissions: Fields are: PermissionID, UserID,
                            CameraID,
                            > > > AllowViewing
                            > > >
                            > > > Table3 - has list of cameras and if they are active
                            > > > TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,
                            > > >
                            > > > Table 4 holds Camera Name
                            > > > Table Name = SYS_Location: Fields Are: LocationID, CameraLocation
                            > > >
                            > > > First off It is not my database so I cannot change the tables in
                            any
                            > > manner.
                            > > >
                            > > > I have written the following query to display a list of cameras
                            that
                            the
                            > > users
                            > > > have.
                            > > >
                            > > > The problem I have is that if I add a camera to the Camera Table
                            it
                            > > does not
                            > > > show up in the list.
                            > > >
                            > > > Can someone help.
                            > > >
                            > > > SELECT Users.UserId, Users.UserName, Users.FullName,
                            > > > Permissions.CameraID,
                            > > > Permissions.AllowViewing, CM_Cameras.Active,
                            > > > SYS_Locations.Location
                            > > > FROM Permissions INNER JOIN
                            > > > Users ON Permissions.UserID = Users.UserId
                            INNER
                            > > JOIN
                            > > > CM_Cameras ON Permissions.CameraID =
                            > > > CM_Cameras.CameraID
                            > > > INNER JOIN
                            > > > SYS_Locations ON CM_Cameras.LocationID =
                            > > > SYS_Locations.LocationID
                            > > > WHERE Users.UserID = :aUserID
                            > > > ORDER BY Users.UserId
                            > > >
                            > > >
                            > > >
                            > > > ------------------------------------
                            > > >
                            > > > Yahoo! Groups Links
                            > > >
                            > > >
                            > > >
                            > >
                            >
                            >
                            >
                            >
                            > ------------------------------------
                            >
                            > Yahoo! Groups Links
                            >
                            >
                            >





                            [Non-text portions of this message have been removed]
                          • Arnie Rowland
                            Use the REPLACE() function, replacing & with & (Note the spaces before and after.) Perhaps something like: SELECT replace( & , (SELECT
                            Message 13 of 18 , Apr 6, 2010
                            • 0 Attachment
                              Use the REPLACE() function, replacing ' & ' with ' & '
                              (Note the spaces before and after.)

                              Perhaps something like:

                              SELECT replace(' & ', (SELECT Vendor_or_Mfg + ', ' as 'data()'
                              FROM [Table_4(Vendor_or_Mfg)] t4
                              WHERE t4.F_documentID=t.F_documentID
                              FOR XML PATH('')) Vendor_or_Mfg
                              ), ' & ' )

                              Regards,

                              Arnie Rowland, MVP (SQL Server)

                              "You cannot do a kindness too soon because you never know how soon it
                              will be too late."
                              -Ralph Waldo Emerson




                              -----Original Message-----
                              From: SQLQueriesNoCode@yahoogroups.com
                              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                              Sent: Tuesday, April 06, 2010 9:38 AM
                              To: Arnie
                              Subject: [SQLQueriesNoCode] RE: UPDATE question

                              First thanks again to all for the suggestions on the UPDATE query
                              yesterday. Works like a charm. But another small problem has popped up
                              and I think this is in SQL Server.

                              One of the fields I hit with my subqueries in the UPDATE is a Name
                              column
                              (vendors) and some of the names have the '&' sign in them such as John
                              & Warner, Inc

                              The SELECT:

                              SELECT Vendor_or_Mfg + ', ' as 'data()'
                              FROM [Table_4(Vendor_or_Mfg)] t4
                              WHERE t4.F_documentID=t.F_documentID
                              FOR XML PATH('')) Vendor_or_Mfg

                              I get John & Warner, Inc

                              I need the & but not the amp. I know the XML is doing this, anyway to
                              make it 'stop' or a simple way without a cursor to pass through the rows
                              returned and replace & with just &? Set preferred to procedural
                              process is what I'm after.

                              John Warner




                              ------------------------------------

                              Yahoo! Groups Links







                              Disclaimer - April 6, 2010
                              This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                              This disclaimer was added by Policy Patrol: http://www.policypatrol.com/


                              [Non-text portions of this message have been removed]
                            • John Warner
                              Thanks Arnie, that wasn t quite it but put me onto the proper trail. I didn t think we could do this with the nesting involved in my query. Oh just so you
                              Message 14 of 18 , Apr 6, 2010
                              • 0 Attachment
                                Thanks Arnie, that wasn't quite it but put me onto the proper trail. I
                                didn't think we could do this with the 'nesting' involved in my query.

                                Oh just so you can see the 'syntax' that works:

                                SELECT REPLACE((SELECT Vendor_or_Mfg + '| ' as 'data()'
                                FROM [Table_4(Vendor_or_Mfg)] t4
                                WHERE t4.F_documentID=t.F_documentID
                                FOR XML PATH('')) ,'&', ' & ' ) Vendor_or_Mfg


                                You have to put it all before the alias.

                                Thanks again!

                                John Warner


                                > -----Original Message-----
                                > From: SQLQueriesNoCode@yahoogroups.com
                                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                > Sent: Tuesday, April 06, 2010 1:27 PM
                                > To: SQLQueriesNoCode@yahoogroups.com
                                > Subject: RE: [SQLQueriesNoCode] RE: UPDATE question
                                >
                                > Use the REPLACE() function, replacing ' & ' with ' & '
                                > (Note the spaces before and after.)
                                >
                                > Perhaps something like:
                                >
                                > SELECT replace(' & ', (SELECT Vendor_or_Mfg + ', ' as 'data()'
                                > FROM [Table_4(Vendor_or_Mfg)] t4
                                > WHERE t4.F_documentID=t.F_documentID
                                > FOR XML PATH('')) Vendor_or_Mfg
                                > ), ' & ' )
                                >
                                > Regards,
                                >
                                > Arnie Rowland, MVP (SQL Server)
                                >
                                > "You cannot do a kindness too soon because you never know how soon it
                                > will be too late."
                                > -Ralph Waldo Emerson
                                >
                                >
                                >
                                >
                                > -----Original Message-----
                                > From: SQLQueriesNoCode@yahoogroups.com
                                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                > Sent: Tuesday, April 06, 2010 9:38 AM
                                > To: Arnie
                                > Subject: [SQLQueriesNoCode] RE: UPDATE question
                                >
                                > First thanks again to all for the suggestions on the UPDATE query
                                > yesterday. Works like a charm. But another small problem has popped up
                                > and I think this is in SQL Server.
                                >
                                > One of the fields I hit with my subqueries in the UPDATE is a Name
                                > column
                                > (vendors) and some of the names have the '&' sign in them such as John
                                > & Warner, Inc
                                >
                                > The SELECT:
                                >
                                > SELECT Vendor_or_Mfg + ', ' as 'data()'
                                > FROM [Table_4(Vendor_or_Mfg)] t4
                                > WHERE t4.F_documentID=t.F_documentID
                                > FOR XML PATH('')) Vendor_or_Mfg
                                >
                                > I get John & Warner, Inc
                                >
                                > I need the & but not the amp. I know the XML is doing this, anyway to
                                > make it 'stop' or a simple way without a cursor to pass through the rows
                                > returned and replace & with just &? Set preferred to procedural
                                > process is what I'm after.
                                >
                                > John Warner
                                >
                                >
                                >
                                >
                                > ------------------------------------
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                >
                                >
                                >
                                >
                                >
                                > Disclaimer - April 6, 2010
                                > This email and any files transmitted with it are confidential and
                                intended solely
                                > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                > addressee you should not disseminate, distribute, copy or alter this
                                email. Any
                                > views or opinions presented in this email are solely those of the author
                                and
                                > might not represent those of Westwood Consulting, Inc. Warning: Although
                                > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                                > viruses are present in this email, the company cannot accept
                                responsibility for
                                > any loss or damage arising from the use of this email or attachments.
                                > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                                >
                                >
                                > [Non-text portions of this message have been removed]
                                >
                                >
                                >
                                > ------------------------------------
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                >
                              • John Warner
                                Using the basic query you have given us, I don t think then that you can just get camera IDs for cameras that don t have users. John Warner
                                Message 15 of 18 , Apr 15, 2010
                                • 0 Attachment
                                  Using the basic query you have given us, I don't think then that you can
                                  just get camera IDs for cameras that don't have users.

                                  John Warner


                                  > -----Original Message-----
                                  > From: SQLQueriesNoCode@yahoogroups.com
                                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of lvs mail
                                  > Sent: Tuesday, April 06, 2010 12:50 PM
                                  > To: SQLQueriesNoCode@yahoogroups.com
                                  > Subject: RE: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP
                                  >
                                  > That does not work either. I get a multitude of Same# UserID with Each
                                  > Carera =ID
                                  >
                                  > Sincerely
                                  >
                                  > Trevor
                                  >
                                  > Cell: (407)467-5435
                                  >
                                  > ________________________________
                                  >
                                  > From: SQLQueriesNoCode@yahoogroups.com
                                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                  > Sent: Tuesday, April 06, 2010 10:15 AM
                                  > To: SQLQueriesNoCode@yahoogroups.com
                                  > Subject: RE: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP
                                  >
                                  >
                                  >
                                  >
                                  >
                                  > Try replacing:
                                  > p.CameraID
                                  >
                                  > with
                                  > c.CameraID
                                  >
                                  > Question when you add a camera, what table(s) are you adding it to since
                                  > 4
                                  > has the name and 3 appears to be the camera table. Not clear where this
                                  > newly added data is to begin with.
                                  >
                                  > John Warner
                                  >
                                  > > -----Original Message-----
                                  > > From: SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                  > > [mailto:SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of
                                  > lv_solutions
                                  > > Sent: Saturday, April 03, 2010 2:23 PM
                                  > > To: SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                  > > Subject: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP
                                  > >
                                  > > It seems as though you fully understand my issue, and thanks for the
                                  > reply.
                                  > >
                                  > > I left off the where, I tried both LEFT and RIGHT but the newly added
                                  > cameras
                                  > > that the users do not have assigned to them and do not have permission
                                  > > assigned are not showing up for the users.
                                  > >
                                  > >
                                  > >
                                  > > --- In SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com> , "John Warner"
                                  > <john@...>
                                  > > wrote:
                                  > > >
                                  > > > If you add a camera it logically would not show up in a list of
                                  > users
                                  > and
                                  > > > view permissions as you have not assigned a user nor permission to
                                  > the
                                  > > > camera. To list cameras that lack this assignment, and OUTER JOIN of
                                  > the
                                  > > > camera table should produce the desired result.
                                  > > >
                                  > > > This all assumes (you know about assumptions) that I have understood
                                  > the
                                  > > > problem correctly.
                                  > > >
                                  > > > SELECT
                                  > > > u.UserId,
                                  > > > u.UserName,
                                  > > > u.FullName,
                                  > > > p.CameraID,
                                  > > > p.AllowViewing,
                                  > > > c.Active,
                                  > > > s.Location
                                  > > > FROM
                                  > > > Permissions p
                                  > > > INNER JOIN Users u
                                  > > > ON p.UserID = u.UserId
                                  > > > LEFT JOIN CM_Cameras c
                                  > > > ON p.CameraID = c.CameraID
                                  > > > INNER JOIN SYS_Locations s
                                  > > > ON c.LocationID = s.LocationID
                                  > > >
                                  > > > WHERE u.UserID = :aUserID (<-???)
                                  > > >
                                  > > > ORDER BY u.UserId
                                  > > >
                                  > > > Note without the actual tables to test, it might be LEFT should be
                                  > > > replaced by RIGHT I always get this 'wrong' on my first try. Also to
                                  > test,
                                  > > > drop the WHERE clause just to see if this gets the camera. Since the
                                  > 'new'
                                  > > > camera would not match a UserID the where might filter out the new
                                  > camera.
                                  > > >
                                  > > > John Warner
                                  > > >
                                  > > >
                                  > > > > -----Original Message-----
                                  > > > > From: SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                  > > > > [mailto:SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of
                                  > > > > CharlesMarkCarroll
                                  > > > > Sent: Friday, April 02, 2010 8:00 PM
                                  > > > > To: SQLQueriesNoCode@yahoogroups.com
                                  > <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                  > > > > Subject: [SQLQueriesNoCode] BEGINNER NEEDS HELP
                                  > > > >
                                  > > > > From: "lv_solutions" <lvs.mail@...>
                                  > > > > Date: Fri Apr 2, 2010 3:29 pm
                                  > > > > Subject: BEGINNER NEEDS HELP
                                  > > > >
                                  > > > >
                                  > > > > Need help please with following Query
                                  > > > >
                                  > > > > Table 1 Holds user info
                                  > > > > Table Name = USERS: Fields are: UserID, UserName, Full Name
                                  > > > >
                                  > > > > Table2 holds permissions for users to view cameras
                                  > > > > Table Name = Permissions: Fields are: PermissionID, UserID,
                                  > CameraID,
                                  > > > > AllowViewing
                                  > > > >
                                  > > > > Table3 - has list of cameras and if they are active
                                  > > > > TambleName = CM_Cameras: Fields are: CameraID, Active, LocationID,
                                  > > > >
                                  > > > > Table 4 holds Camera Name
                                  > > > > Table Name = SYS_Location: Fields Are: LocationID, CameraLocation
                                  > > > >
                                  > > > > First off It is not my database so I cannot change the tables in
                                  > any
                                  > > > manner.
                                  > > > >
                                  > > > > I have written the following query to display a list of cameras
                                  > that
                                  > the
                                  > > > users
                                  > > > > have.
                                  > > > >
                                  > > > > The problem I have is that if I add a camera to the Camera Table
                                  > it
                                  > > > does not
                                  > > > > show up in the list.
                                  > > > >
                                  > > > > Can someone help.
                                  > > > >
                                  > > > > SELECT Users.UserId, Users.UserName, Users.FullName,
                                  > > > > Permissions.CameraID,
                                  > > > > Permissions.AllowViewing, CM_Cameras.Active,
                                  > > > > SYS_Locations.Location
                                  > > > > FROM Permissions INNER JOIN
                                  > > > > Users ON Permissions.UserID = Users.UserId
                                  > INNER
                                  > > > JOIN
                                  > > > > CM_Cameras ON Permissions.CameraID =
                                  > > > > CM_Cameras.CameraID
                                  > > > > INNER JOIN
                                  > > > > SYS_Locations ON CM_Cameras.LocationID =
                                  > > > > SYS_Locations.LocationID
                                  > > > > WHERE Users.UserID = :aUserID
                                  > > > > ORDER BY Users.UserId
                                  > > > >
                                  > > > >
                                  > > > >
                                  > > > > ------------------------------------
                                  > > > >
                                  > > > > Yahoo! Groups Links
                                  > > > >
                                  > > > >
                                  > > > >
                                  > > >
                                  > >
                                  > >
                                  > >
                                  > >
                                  > > ------------------------------------
                                  > >
                                  > > Yahoo! Groups Links
                                  > >
                                  > >
                                  > >
                                  >
                                  >
                                  >
                                  >
                                  >
                                  > [Non-text portions of this message have been removed]
                                  >
                                  >
                                  >
                                  > ------------------------------------
                                  >
                                  > Yahoo! Groups Links
                                  >
                                  >
                                  >
                                • Rob Richardson
                                  I have found that if I need to get information about something that doesn t have matching data somewhere else in my query, the first thing I have to do is
                                  Message 16 of 18 , Apr 16, 2010
                                  • 0 Attachment
                                    I have found that if I need to get information about something that
                                    doesn't have matching data somewhere else in my query, the first thing I
                                    have to do is rearrange my query so that the table that may or may not
                                    have data is first. Then, outer joins can bring in the rest of the data
                                    you need.

                                    So, for your query, I'd do something along the lines of:

                                    SELECT some_fields
                                    FROM camera
                                    LEFT OUTER JOIN users ON camera.some_key = users.some_permission

                                    Maybe that's enough to give you the idea.

                                    RobR
                                  • Noman Aftab
                                    Hi, I have a non-query database related question: We have a database in SQL Server 2005 whose MDF file was increased to 300 GB. The system is used for
                                    Message 17 of 18 , May 5 5:27 AM
                                    • 0 Attachment
                                      Hi,
                                      I have a non-query database related question:
                                      We have a database in SQL Server 2005 whose MDF file was increased to 300 GB. The system is used for reporting purposes only. We deleted somewhat 80% of the data through delete scripts but the file size of MDF didn't decreased. We were able to shrink the LDF file to a minimum (1MB), but the size for MDF is not decreasing by shrinking the database. Any suggestions?
                                      Best Wishes,
                                      Noman Aftab


                                      http://www.corpus.quran.com/wordbyword.jsp
                                      http://www.jalandhari.qsh.eu



                                      [Non-text portions of this message have been removed]
                                    • p_livengood
                                      You have to shrink the database, then then file --first run this DBCC SHRINKDATABASE (dbName) --then run this DBCC SHRINKFILE (logicalLogFileName) HTH Paul
                                      Message 18 of 18 , May 5 7:43 AM
                                      • 0 Attachment
                                        You have to shrink the database, then then file

                                        --first run this
                                        DBCC SHRINKDATABASE (dbName)

                                        --then run this
                                        DBCC SHRINKFILE (logicalLogFileName)

                                        HTH
                                        Paul
                                        --- In SQLQueriesNoCode@yahoogroups.com, Noman Aftab <noman17pk@...> wrote:
                                        >
                                        > Hi,
                                        > I have a non-query database related question:
                                        > We have a database in SQL Server 2005 whose MDF file was increased to 300 GB. The system is used for reporting purposes only. We deleted somewhat 80% of the data through delete scripts but the file size of MDF didn't decreased. We were able to shrink the LDF file to a minimum (1MB), but the size for MDF is not decreasing by shrinking the database. Any suggestions?
                                        > Best Wishes,
                                        > Noman Aftab
                                        >
                                        >
                                        > http://www.corpus.quran.com/wordbyword.jsp
                                        > http://www.jalandhari.qsh.eu
                                        >
                                        >
                                        >
                                        > [Non-text portions of this message have been removed]
                                        >
                                      Your message has been successfully submitted and would be delivered to recipients shortly.