Need SQL query for joining table and get desired output

Question

0.00/5 (No votes)

See more:

I have two tables 'table 1' and 'table 2'. I have to join those tables to get the desired output.

table 1

COLUMN1 COLUMN2
    A   1
    A   2
    A   3
    A   4
    A   5
    A   6
    A   7
    A   8
    B   9
    B   10
    B   11
    B   12
    B   13
    B   14
    B   15
    B   16
    C   17
    C   18
    C   19
    C   20
    C   21
    C   22
    C   23
    C   24

table 2

COLUMN1 COLUMN2   COLUMN3 COLUMN4
    DAY1    A          1    TEST1
    DAY1    A          3    TEST3
    DAY1    A          6    TEST6
    DAY1    A          8    TEST8
    DAY1    B          9    TEST9
    DAY1    B         11    TEST11
    DAY1    B         14    TEST14
    DAY1    C         18    TEST18
    DAY1    C         19    TEST19
    DAY1    C         23    TEST23
    DAY2    A          2    TEST1
    DAY2    A          2    TEST3
    DAY2    A          7    TEST6
    DAY2    A          8    TEST8
    DAY2    B          11   TEST9
    DAY2    B          13   TEST11
    DAY2    B          16   TEST14
    DAY2    C          17   TEST18
    DAY2    C          21   TEST19
    DAY2    C          24   TEST23

Table 1 is my master table so it never change. Table 2 keeps changing everyday. I have to join two tables to get the expected result below

Expected Result

COLUMN1 COLUMN2 COLUMN3 COLUMN4
DAY1        A      1    TEST1
DAY1        A      2    TEST1
DAY1        A      3    TEST3
DAY1        A      4    TEST3
DAY1        A      5    TEST3
DAY1        A      6    TEST6
DAY1        A      7    TEST6
DAY1        A      8    TEST8
DAY1        B      9    TEST9
DAY1        B      10   TEST9
DAY1        B      11   TEST11
DAY1        B      12   TEST11
DAY1        B      13   TEST11
DAY1        B      14   TEST14
DAY1        B      15   TEST14
DAY1        B      16   TEST14
DAY1        C      17   TEST14
DAY1        C      18   TEST18
DAY1        C      19   TEST19
DAY1        C      20   TEST19
DAY1        C      21   TEST19
DAY1        C      22   TEST19
DAY1        C      23   TEST23
DAY1        C      24   TEST23
DAY2        A      1    TEST1
DAY2        A      2    TEST1
DAY2        A      3    TEST3
DAY2        A      4    TEST3
DAY2        A      5    TEST3
DAY2        A      6    TEST6
DAY2        A      7    TEST6
DAY2        A      8    TEST8
DAY2        B      9    TEST9
DAY2        B      10   TEST9
DAY2        B      11   TEST11
DAY2        B      12   TEST11
DAY2        B      13   TEST11
DAY2        B      14   TEST14
DAY2        B      15   TEST14
DAY2        B      16   TEST14
DAY2        C      17   TEST14
DAY2        C      18   TEST18
DAY2        C      19   TEST19
DAY2        C      20   TEST19
DAY2        C      21   TEST19
DAY2        C      22   TEST19
DAY2        C      23   TEST23
DAY2        C      24   TEST23

After joining Table 1 and Table 2, some rows of Column 4 in Expected result table will have Null Values. Where ever it is Null it should take Previous Row Column 4 value. Hope my Query is clears

What I have tried:

table 1 is the master table and table 2 gets changed everyday. Since am new to SQL sever, i just tried table 1 left join table 2 and table 2 left join table 1 but it is not giving me the expected result

Posted 27-Oct-19 21:15pm

Member 14636607

Updated 28-Oct-19 1:59am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-10-28T01:59:00

Your data does not support this with a simple JOIN query; quickest route I see is to UNION two joins together, and work from there for fine tuning. Here's a starter for you

SQL

SELECT b.Column1, b.Column2, b.Column3, b.Column4
FROM       Table1  a
INNER JOIN Table2  b ON a.Column1 = b.Column2 AND a.Column2 = b.Column3
   UNION
SELECT b.Column1, b.Column2, b.Column3, b.Column4
FROM       Table1  a
INNER JOIN Table2  b ON a.Column1 = b.Column2 AND a.Column2 = (b.Column3 -1)