User Tools

Site Tools


podoc:view_users_subscribed_to_sql_view

Database View Description of "view_users_subscribed_to"

Name

view_users_subscribed_to

Description

Tentative description: I'm not completely sure how this all goes. So here is an attempt to describe what the view is doing. I may be completely wrong as I'm not sure what the users and the client fields in the client table actually mean. Does the client.client field refer to the user of which that row is a client or does it mean that that user is the client of the client.users user?

This view lists the users that have clients.

There are three tables involved: client, users and client status. If we look at the WHERE clause of the SQL statement we see that only those rows from the table users and the table client are selected where client.users matches users.identifier. This is an inner join that basically filters out only those users that are included in the client table. The next where clause reduces the list to only those entries that have a valid status.

Now the question is if we will see users multiple times if they have multiple clients or not?

Purpose

Schema

CREATE VIEW view_users_subscribed_to AS
  SELECT client.identifier, client.users, client.client, users.last_name, users.first_name, date_of_creation, client_status.VALUE, client.STATUS
    FROM client, users, client_status
   WHERE client.users = users.identifier
     AND client.STATUS = client_status.identifier
   ORDER BY users.last_name, users.first_name;

History

Used first

Version 2.5

Used last

Still in use

Change history

= Version 2.5 =

CREATE VIEW view_users_subscribed_to AS
  SELECT client.identifier, client.users, client.client, users.last_name, users.first_name, date_of_creation, client_status.VALUE, client.STATUS
    FROM client, users, client_status
   WHERE client.users = users.identifier
     AND client.STATUS = client_status.identifier
   ORDER BY users.last_name, users.first_name;
podoc/view_users_subscribed_to_sql_view.txt · Last modified: 2007/04/23 10:23 by Luud