Odtnhj

I am trying to do a query on three different tables.

1. 
   

   The variable table

   
   
   
   • The variable table carries information about what "area", "rounds" and
     "days" the variable belongs to. The variable table also holds a pk column.
     The pk is used to determine which variable a record belongs to.
   
   


2. 
   

   The area table

   
   
   
   • The area table carries information about the "name" of the area as well as
     the "role" the area belongs to. A user is assigned a role and then has
     access to specific areas.
   
   


3. 
   

   The record table

   
   
   
   • The record table carries information about the record that was entered. It
     contains the "value", "alarmed", and "alarmType" columns. You can search
     for a record based on the variable, round and day.
   
   

I am trying to query all of the variables in a certain round and day for a user.
I want to display all the variables whether or not there is a record found. Currently I have a query that only returns the variables that have records, but not the ones that don't.

If there is no record then thevalue, alarmed, and alarmType column should be null.

This is the query that I have so far constructed:

SELECT DISTINCT variable.name, area.name AS "areaName", variable.pk, CAST(record.value AS TEXT) AS "value", record.alarmed, record.alarmType 
FROM variable, area, record 
WHERE variable.round LIKE '%,1,%' 
 AND variable.day LIKE '%,3,%' 
 AND variable.readOnly = 0 
 AND variable.area IN (SELECT pk 
 FROM area 
 WHERE role = (SELECT role 
 FROM user 
 WHERE userName LIKE 'Leo')) 
 AND variable.area = area.pk 
 AND record.value = (SELECT CASE WHEN COUNT() < 1 THEN NULL 
 ELSE CAST(value AS TEXT) END 
 FROM record 
 WHERE round = 1 
 AND day = "11-14-2018" 
 AND variable = variable.pk) 
ORDER BY variable.area, variable.position ASC;

Currently it returns something like this:

There are a lot more variables and I want to know how to display them even if there are no records.

I think I see what you're trying to do. The key is using joins (specifically OUTER joins) instead of trying to mash all the tables together and then find similarities. There are also LEFT, RIGHT and INNER flavors (read more about these here and here), depending on what you consider the "complete" or "master" data set - the starting point of your query.

Here's how I understand your relationships (let me know if I have this wrong):

record.variable --> variable.pk
variable.area --> area.pk
area.role --> user.role

In your case, you stated that you need all records from the variable table, so I would start with this:

SELECT v.*
FROM variable v;

Then, you might find all the AREA records related to a particular USER. Use an INNER join to find only records that exist on BOTH sides of the join:

SELECT a.*, u.*
FROM area a
INNER JOIN user u -- Define the table to join
 ON a.role = u.role -- Which columns contain keys to match on
WHERE u.userName = 'Leo';

The WHERE filter applies to the user table, but because we are ONLY asking for records from the area table that have a match with user, then that limits the results from the area table.

The next step is to join these two extracts together using another INNER join, again, to find the intersection - matches that exist on BOTH sides of the join(s):

SELECT v.*, a.*, u.*
FROM variable v -- New starting point
INNER JOIN area a
 ON a.pk = v.area
INNER JOIN user u
 ON a.role = u.role
WHERE u.userName = 'Leo';

Now, we find all the records for a certain day by adding WHERE clauses:

SELECT v.*, a.*, u.*
FROM variable v
INNER JOIN area a
 ON a.pk = v.area
INNER JOIN user u
 ON a.role = u.role
WHERE u.userName = 'Leo'
AND v.round = 1 -- Add filters for "round"
AND v.day = '11-14-2018'; -- and "day" columns

Next, we use a LEFT join to give us all the records from the table on the "left" plus any matches we find on the "right" side (the "record" table) or NULL if no match is made:

SELECT v.name
 ,a.name as "areaName"
 ,CAST(r.value as TEXT) as "value"
 ,r.alarmed
 ,r.alarmType
FROM variable v
INNER JOIN area a
 ON v.area = a.pk
INNER JOIN user u
 ON a.role = u.role
LEFT JOIN record r -- LEFT is important here
 ON v.pk = r.variable
WHERE u.userName = 'Leo'
AND v.round = 1
AND v.day = '11-14-2018'
ORDER BY v.area, v.position;

The result from INNER joins (variable + area + user) becomes the "left" side of this join, and the record becomes the "right" side. Using the LEFT join declares that we want ALL records from the left, whether they have a match on the right or not.

I don't have a dataset to test this with, so please excuse any errors I've made.

Hopefully, this illustrates how joins would be used to both eliminate rows and add data (columns) the result, without having to make individual queries or resort to sub-queries (using IN or EXISTS).