Preamble
Given the problems that I regularly ask colleagues and users, I have tried to solve the case of a family problems rather difficult to express in SQL simple. I came to the conclusion that it was interesting to foresee adding a new SQL join type, the seam "LINEAR". We will as a first step pose different problems and see how we can solve this by introducing new operator.
In the immediate I do not venture into the relational algebra to determine whether the good doctor Codd has failed at its task. I therefore reserve the theorists a great battle in perspective to determine whether this operation and how to represent ...
1. Number lines and all queries arising
Either the table T_CLIENT_CLI (CLI_ID, CLI_NOM) as follows:
CLI_ID CLI_NOM
------- -----------
17 DURAND
192 DUPONT
44 DUVAL
11 DUMOULIN
741 DULIN
82 DUPOND
177 DURAND
Creation of the test Thursday:
CREATE TABLE T_CLIENT_CLI
(CLI_ID INTEGER,
CLI_NOM VARCHAR(10))
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (17, 'DURAND')
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (192, 'DUPONT')
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (44, 'DUVAL')
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (11, 'DUMOULIN')
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (741, 'DULIN')
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (82, 'DUPOND')
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM)
VALUES (177, 'DURAND'
The question is: how to get a response the names of our customers in alphabetical order with their rank (from 1 to n)?
1.1. RESPONSE 1
If we apply strictly the issue, then the answer is:
CLI_NOM RANG
------------- -----
DULIN 1
DUMOULIN 2
DUPOND 3
DUPONT 4
DURAND 5
DURAND 5
DUVAL 7
Indeed, both located DURAND tie occupy the 5th place, while Duval has not the 6th, but the 7th row!
1.2. ANSWER 2
Another possible solution is:
CLI_NOM RANG
------------- -----
DULIN 1
DUMOULIN 2
DUPOND 3
DUPONT 4
DURAND 5
DURAND 6
DUVAL 7
This means a frank and direct numbering regardless of duplication or ambiguity selected information. It's a bit what are the columns of auto increment of certain RDBMS.
1.3. RESPONSE 3
Finally we can refine the latter solution by introducing a counting to eliminate duplication:
CLI_NOM RANG NUMBER
------------- ----- ------
DULIN 1 1
DUMOULIN 2 1
DUPOND 3 1
DUPONT 4 1
DURAND 5 2
DUVAL 7 1
Who deserves to be more clean match, however, the initial application!
1.4. ANSWER 4
By pushing things to extremes, it may require that the numbering category is strict and without "hole", like this:
CLI_NOM RANG NUMBER
------------- ----- ------
DULIN 1 1
DUMOULIN 2 1
DUPOND 3 1
DUPONT 4 1
DURAND 5 2
DUVAL 6 1
But what are the applications necessary to achieve the various solutions proposed?
1.5. Queries
In principle, requests to meet such demand require auto non equivalent to join the enumeration of tuples whose values above the tuple underway.
Query the response 1:
SELECT T1.CLI_NOM, COUNT(T2.CLI_ID) + 1 AS RANG
FROM T_CLIENT_CLI T1
LEFT OUTER JOIN T_CLIENT_CLI T2
ON T1.CLI_NOM > T2.CLI_NOM
GROUP BY T1.CLI_ID, T1.CLI_NOM
ORDER BY RANG
Query the answer 2:
SELECT T1.CLI_NOM, COUNT(T2.CLI_ID) + 1 AS RANG
FROM T_CLIENT_CLI T1
LEFT OUTER JOIN T_CLIENT_CLI T2
ON T1.CLI_NOM || CAST(T1.CLI_ID AS CHAR(16))
> T2.CLI_NOM || CAST(T2.CLI_ID AS CHAR(16))
GROUP BY T1.CLI_ID, T1.CLI_NOM
ORDER BY RANG
Query the response 3:
SELECT T1.CLI_NOM, RANG, NOMBRE
FROM (SELECT DISTINCT T1.CLI_NOM, COUNT(T2.CLI_ID) + 1 AS RANG
FROM T_CLIENT_CLI T1
LEFT OUTER JOIN T_CLIENT_CLI T2
ON T1.CLI_NOM > T2.CLI_NOM
GROUP BY T1.CLI_ID, T1.CLI_NOM)
T1
INNER JOIN (SELECT CLI_NOM, COUNT(CLI_ID) AS NOMBRE
FROM T_CLIENT_CLI T1
GROUP BY CLI_NOM) T2
ON T1.CLI_NOM = T2.CLI_NOM
ORDER BY RANG
Query the response 4:
SELECT T2.CLI_NOM, COUNT(T1.CLI_NOM) AS RANG, NOMBRE
FROM (SELECT DISTINCT CLI_NOM
FROM T_CLIENT_CLI) T1
LEFT OUTER JOIN (SELECT DISTINCT CLI_NOM
FROM T_CLIENT_CLI) T2
ON T1.CLI_NOM <= T2.CLI_NOM
INNER JOIN (SELECT CLI_NOM, COUNT(CLI_ID) AS NOMBRE
FROM T_CLIENT_CLI
GROUP BY CLI_NOM) T3
ON T2.CLI_NOM = T3.CLI_NOM
GROUP BY T2.CLI_NOM, T3.NOMBRE
ORDER BY RANG
The least we can say is that this kind of requests is not in the spirit of starting. What then developer facing this problem in a context of tables far more extensive than our example? It is very likely that the latter passes by hand and lunges a nice stored procedure at best and at worst on the client!
2. Assign lines places
The second family problems that merit our attention in this context, the problems of employment, issues dear to all teachers at the beginning of the school year, for example. The question is: starting up a table and a population consisting of another place (each place is intended to accommodate a student, a spectator…) how to assign a place for every element of the population?
Reclaiming our table of customers and add a table seating model places a theatre T_PLACE_PLC (PLC_REF). The theater seats are numbered as you know by letters (rank) and figures (in rank order). For our demonstration we will limit to three ranks and 5 seats per row, ie a theatre pocket!
PLC_REF
-------
A01
A02
A03
A04
A05
B01
B02
B03
B04
B05
C01
C02
C03
C04
C05
Creation of the test Thursday:
CREATE TABLE T_PLACE_PLC
(PLC_REF CHAR(3))
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('A01')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('A02')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('A03')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('A04')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('A05')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('B01')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('B02')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('B03')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('B04')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('B05')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('C01')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('C02')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('C03')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('C04')
INSERT INTO T_PLACE_PLC (PLC_REF) VALUES ('C05')
The problem could not be simpler: how to allocate customers in the first seats?
It seems clear that the simplest solution would be numbered lines of customers and then rows of seats and make a join with the numbering.
Something like:
CLI_ID CLI_NOM CLI_NUM
------------ ------- -------
17 DURAND 1
192 DUPONT 2
44 DUVAL 3
11 DUMOULIN 4
741 DULIN 5
82 DUPOND 6
177 DURAND 7
PLC_REF PLC_NUM
-------- -------
A01 1
A02 2
A03 3
A04 4
A05 5
B01 6
B02 7
B03 8
B04 9
B05 10
C01 11
C02 12
C03 13
C04 14
C05 15
Therefore, the solution is obvious:
SELECT CLI_NOM, PLC_REF
FROM T_CLIENT_CLI
JOIN T_PLACE_PLC
ON CLI_NUM = PLC_NUM Who gives:
CLI_NOM PLC_REF
------------ -------
DURAND A01
DUPONT A02
DUVAL A03
DUMOULIN A04
DULIN A05
DUPOND B01
DURAND B02
But we do not have these columns available… How?
Just apply what we have just seen in the previous example, for both customers, but also for chairs and join all on the columns of numbers generated.
I request the book as it stood, its development is quite happy!
SELECT CLI_NOM, PLC_REF
FROM (SELECT T1.CLI_NOM, COUNT(T2.CLI_ID) + 1 AS RANG
FROM T_CLIENT_CLI T1
LEFT OUTER JOIN T_CLIENT_CLI T2
ON T1.CLI_NOM || CAST(T1.CLI_ID AS CHAR(16))
> T2.CLI_NOM || CAST(T2.CLI_ID AS CHAR(16))
GROUP BY T1.CLI_ID, T1.CLI_NOM) C
INNER JOIN (SELECT T3.PLC_REF, COUNT(T4.PLC_REF) + 1 AS RANG
FROM T_PLACE_PLC T3
LEFT OUTER JOIN T_PLACE_PLC T4
ON T3.PLC_REF > T4.PLC_REF
GROUP BY T3.PLC_REF) P
ON C.RANG = P.RANG
And yet we have taken into account that the column PLC_REF is a key candidate of the table T_PLACE_PLC ...
3. The solution, the seam line!
The primal condition is to have a very simple table with a single column and filled with the following numbers: T_I_ENT (ENT_I). Of course there will be limited for example to a range of 0 to 1000 or more as needed:
ENT_I
-------
0
1
2
3
4
5
6
7
8
9
10
...
CREATE TABLE T_I_ENT
(ENT_I INTEGER)
INSERT INTO T_I_ENT (ENT_I) VALUES (0)
INSERT INTO T_I_ENT (ENT_I) VALUES (1)
INSERT INTO T_I_ENT (ENT_I) VALUES (2)
INSERT INTO T_I_ENT (ENT_I) VALUES (3)
INSERT INTO T_I_ENT (ENT_I) VALUES (4)
INSERT INTO T_I_ENT (ENT_I) VALUES (5)
INSERT INTO T_I_ENT (ENT_I) VALUES (6)
INSERT INTO T_I_ENT (ENT_I) VALUES (7)
INSERT INTO T_I_ENT (ENT_I) VALUES (8)
INSERT INTO T_I_ENT (ENT_I) VALUES (9)
INSERT INTO T_I_ENT (ENT_I) VALUES (10)
...
INSERT INTO T_I_ENT (ENT_I) VALUES (1000)
We note in passing that it is unnecessary to enter all the numbers from 1 to 1000, the first ten enough and a simple application integration will play the role of complementary insertion:
INSERT INTO T_I_ENT (ENT_I)
SELECT DISTINCT I1.ENT_I + (I2.ENT_I * 10) + (I3.ENT_I * 100)
FROM T_I_ENT I1
CROSS JOIN T_I_ENT I2
CROSS JOIN T_I_ENT I3
CROSS JOIN T_I_ENT I4
WHERE I1.ENT_I + (I2.ENT_I * 10) + (I3.ENT_I * 100) BETWEEN 0 AND 1000
Thus the juxtaposition of the projection by the name of the customer table ordered by the customer with the projection of the whole table ordered meets our expectations:
CLI_NOM T_I_ENT
---------- ------------
DULIN 1
DUMOULIN 2
DUPOND 3
DUPONT 4
DURAND 5
DURAND 6
DUVAL 7
That is why I propose the new operator join linear: LINEAR JOIN to match the line of a row of the table left, the line rang a + offset the table right and so forth.
4. Syntax and rules of the seam line
The seam line meets the following syntax:
SELECT <selection list>
FROM <table gauche>
[LEFT | RIGHT] LINEAR JOIN <table right > [OFFSET <offset value>]
In our previous example, you just have to do:
SELECT CLI_NOM, T_I_ENT
FROM T_CLIENT_CLI
LINEAR JOIN T_I_ENT OFFSET 1 /* Elimination of the first line, a zero*/
ORDER BY CLI_NOM, T_I_ENT
Some explanations:
* Keyword OFFSET can tell from what line to take into account the first row of the table right associated with the first line of the table left
* ORDER BY clause operates separately before the joint linear tables attached
* The joint can be left outside (LEFT JOIN LINEAR) or right (RIGHT JOIN LINEAR), but not on both sides. By default it is internal
Example join linear external right:
SELECT CLI_NOM, T_I_ENT
FROM T_CLIENT_CLI
RIGHT LINEAR JOIN T_I_ENT
ORDER BY CLI_NOM, T_I_ENT
which gives:
CLI_NOM T_I_ENT
---------- ------------
DULIN 0
DUMOULIN 1
DUPOND 2
DUPONT 3
DURAND 4
DURAND 5
DUVAL 6
NULL 7
NULL 8
...
NULL 1000
To solve our problem of allocating places theatre, just do:
SELECT CLI_NOM, PLC_REF
FROM T_CLIENT_CLI
LINEAR JOIN T_I_ENT
LINEAR JOIN T_PLACE_PLC
ORDER BY CLI_NOM, T_I_ENT, PLC_REF
I do not know what you think but I find this writing simple and easy to understand!
5. CONCLUSION
These complaints are similar to the T-JOIN (theta joints ) Dr. Codd to obtain an optimal correspondence inequalities (typically the problem of allocating students in rooms capacity data).
