For each input and output table, find its value. Those. a statement template, parameterized by column names, aka predicate what a row makes into a true or false statement, aka a sentence. The table contains rows that turn its predicate into a true sentence. Those. rows that make a true sentence go to the table and rows that make a false sentence. For example, for an input table:
rental [tableid] was user [userid] renting car [car] from [from] to [to]
Then we express the predicate of the output table in terms of the predicate of the input table. Do not use descriptions like yours 1 and 2:
- If any user has a date match in their car assignments for more than one day (the end of the task may be on the same day when a new appointment begins).
Instead, find the predicate that an arbitrary row is specified in the table:
rental [tableid] was user [user] renting car [car] from [from] to [to] in self-conflict with some other rental
In order for the DBMS to calculate the lines that make it true, we must express it in terms of our predicate (s) plus literals and conditions:
-- query result holds the rows where FOR SOME t2.tableid, t2.userid, ...: rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to] AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to] AND [t1.userid] = [t2.userid] -- userids id the same users AND [t1.to] > [t2.from] AND ... -- tos/froms id intervals with overlap more than one day ...
(Inside a SQL SELECT , a cross-sectional JOIN ed statement has column names of the form alias . column . Think of it as another character allowed in column names. The SELECT disables alias . S.)
We convert the query predicate to an SQL query that evaluates the rows that make it true:
- A table predicate is replaced by a table alias.
- To use the same predicate / table multiple times, create aliases.
- Changing the
old column to new in the predicate adds AND old = new . AND predicates are replaced by JOIN .OR predicates are replaced by UNION .AND NOT predicates are replaced with EXCEPT , MINUS or the corresponding LEFT JOIN .AND condition is replaced with WHERE or ON condition .- For the predicate true
FOR SOME columns to drop or when THERE EXISTS columns to drop , SELECT DISTINCT columns to keep . - Etc. (See this one .)
From here (completion of ellipses):
SELECT DISTINCT t1.* FROM t t1 JOIN t t2 ON t1.userid = t1.userid
- Whether there were any two users trying to get the same car assigned on the same date, or the date ranges overlap for them on the same car.
Search for the predicate, which in the line indicates an arbitrary line:
rental [tableid] was user [user] renting car [car] from [from] to [to] in conflict with some other user rental
In terms of our predicate (s) plus literals and conditions:
-- query result holds the rows where FOR SOME t2.* rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to] AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to] AND [t1.userid] <> [t2.userid] -- userids id different users AND [t1.car] = [t2.car] -- .cars id the same car AND [t1.to] >= [t2.from] AND [t2.to] >= [t1.from] -- tos/froms id intervals with any overlap AND [t1.tableid] <> [t2.tableid] -- tableids id different rentals
UNION queries for predicates 1 and 2 returns rows for which predicate 1 OR predicate 2 .
Try to learn how to express predicates - which rows are in the tables - if only as a goal for an intuitive (auxiliary) query.
PS It’s good to always have data to check boundaries and cases without an edge for a condition that is true and false. For example, try query 1 with a GTR starting on the 31st, blocking only one day, which should not be self-conflicting.
PPS A query using duplicate strings, as for NULL, has rather complex query values. It is difficult to say when a tuple enters or stays off the table and how many times. For queries that have simple intuitive meanings for my matches, they cannot have duplicates. Here, unfortunately, SQL differs from the relational model. In practice, people rely on idioms when they allow fuzzy lines, and they rely on lines that differ due to limitations. For example, joins UNIQUE columns on UNIQUE, PK, and FK. For example: the last step of DISTINCT works only at different times than the version that it does not need; time may or may not be an important implementation problem that affects the wording chosen for a given predicate / result.