Marius Marius -4 years ago 75
SQL Question

SQL Right Join issue

My query make a Right Join, but the results is not correct.

Null
values must be where column
NAME_2
is equal with
A
and I can't figure out what is wrong.

Here is the query:

http://sqlfiddle.com/#!6/cd45b/1

For the future readers. The definition of tables:

CREATE TABLE S1 (TIME_1 DateTIME, INTERVAL_1 INT, NAME_1 varchar(20),INITIAL_VAL int);
CREATE TABLE S2 (TIME_2 DateTIME, INTERVAL_2 INT, NAME_2 varchar(20), FINAL_VAL int);


The data inserts related to the question:

INSERT INTO S1 values('10.02.2017 00:00',1, 'B',13);
INSERT INTO S1 values('10.02.2017 01:00',2, 'B',14);
INSERT INTO S1 values('10.02.2017 02:00',3, 'B',10);
INSERT INTO S1 values('10.02.2017 03:00',4, 'B',15);

INSERT INTO S1 values('10.02.2017 00:00',1, 'C',42);
INSERT INTO S1 values('10.02.2017 01:00',2, 'C',10);
INSERT INTO S1 values('10.02.2017 02:00',3, 'C',22);
INSERT INTO S1 values('10.02.2017 03:00',4, 'C',18);


INSERT INTO S2 values('10.02.2017 00:00',1, 'A',12);
INSERT INTO S2 values('10.02.2017 01:00',2, 'A',13);
INSERT INTO S2 values('10.02.2017 02:00',3, 'A',9);
INSERT INTO S2 values('10.02.2017 03:00',4, 'A',16);

INSERT INTO S2 values('10.02.2017 00:00',1, 'B',16);
INSERT INTO S2 values('10.02.2017 01:00',2, 'B',22);
INSERT INTO S2 values('10.02.2017 02:00',3, 'B',20);
INSERT INTO S2 values('10.02.2017 03:00',4, 'B',10);

INSERT INTO S2 values('10.02.2017 00:00',1, 'C',42);
INSERT INTO S2 values('10.02.2017 01:00',2, 'C',15);
INSERT INTO S2 values('10.02.2017 02:00',3, 'C',22);
INSERT INTO S2 values('10.02.2017 03:00',4, 'C',19);


Initial query from the question:

WITH CTE1
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY NAME_1) AS V1, * FROM S1
)
,CTE2
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY NAME_2) AS V2,* FROM S2
)
SELECT
CTE2.NAME_2,
CTE2.TIME_2,
INTERVAL_2,
CTE1.INITIAL_VAL,
CTE2.FINAL_VAL
FROM CTE1
RIGHT JOIN CTE2
ON CTE1.V1=CTE2.V2

Answer Source

Your right join is matching over ROW_NUMBER produced. So it doesn't matter that it's NAME_2 value: A, doesn't have a match with NAME_1 value.

It is matching A from table S1 with B from tableS2, because they both get the same ROW_NUMBER

Please run this to see what is matched when you do right join:

SELECT ROW_NUMBER() OVER (ORDER BY NAME_1) AS V1, * FROM S1;
SELECT ROW_NUMBER() OVER (ORDER BY NAME_2) AS V2,* FROM S2;

Edit: Answering to your question from the comment. It is very hard to know what exactly do you need to build, but I think that the interval might bring some interesting results:

WITH CTE1
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY NAME_1) AS V1, * FROM S1
)
,CTE2
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY NAME_2) AS V2,* FROM S2
)
SELECT
CTE2.NAME_2,
CTE2.TIME_2,
INTERVAL_2,
CTE1.INITIAL_VAL,
CTE2.FINAL_VAL
FROM CTE1
RIGHT JOIN CTE2
ON CTE1.NAME_1=CTE2.NAME_2 AND CTE1.INTERVAL_1 = CTE2.INTERVAL_2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download