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

4499RE: [SQLQueriesNoCode] Re: BEGINNER NEEDS HELP

Expand Messages
  • John Warner
    Apr 6 7:15 AM
    • 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
      >
      >
      >
    • Show all 18 messages in this topic