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

4498Re: BEGINNER NEEDS HELP

Expand Messages
  • lv_solutions
    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
      > >
      > >
      > >
      >
    • Show all 18 messages in this topic