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

Re: BEGINNER NEEDS HELP

Expand Messages
  • 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 1 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
      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 2 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
        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 3 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 4 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 5 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 6 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 7 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 8 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 9 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 10 of 18 , May 5, 2010
                      • 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 11 of 18 , May 5, 2010
                        • 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.