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


Expand Messages
  • John Warner
    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.

      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
      > I have written the following query to display a list of cameras that the
      > 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
      > 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