I have two tables that I want to combine.

enter image description here

I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the last record.

Result should be:

enter image description here

I am new to SQL and with my research, it seems I need to use UNION to join both tables and to join the new table (from Union) by itself. When I tried doing that, it created duplicate rows and columns.

  • seems like all you need is an INNER JOIN and not UNION. Each person has the same code/id (Ann has ID = 1 and Code = 1, Mike ID= 3, Code = 8 in both etc...). So just inner join both tables on the ID field. Maybe that gives you the desired result – Chessbrain Jul 19 '19 at 15:37
  • 5
    Welcome to that site, let me suggest to have a look at: How to create a Minimal, Complete, and Verifiable Example for database-related questions – McNets Jul 19 '19 at 15:37
  • @Chessbrain, it did not. i tried doing that and it duplicates the column and rows. I must have done something wrong somewhere on the code. – ann tot Jul 22 '19 at 3:31
  • Can you please add sample result? it will help to understand your requirement. – Rajesh Ranjan Jul 22 '19 at 3:56
  • I think you should use INNER JOIN and specify columns name in the select statement. – Rajesh Ranjan Jul 22 '19 at 7:09

You are correct - if you have two datasets with identical columns then UNION is the correct way to append the second dataset onto the first. Moreover I would suggest you use UNION ALL. UNION by itself guarantees unique values in the output. Usually this requires a sort and further processing to detect and eliminate duplicates. In your case I do not think you have duplicates so UNION ALL would be appropriate.

The real problem seems to be the sort order. You want the combined dataset to be sorted by Record but with 0 as the last item. The rows exist in two blocks: the zero rows and the other rows. If we assign a "block number" to each they can be sorted accordingly. Clearly this cannot be achieved by changing Record itself so another column must be introduced. This new artificial column need not be returned form the query.

You don't mention which DBMS you use. I have SQL Server to hand; I'll use it. For future questions please do not include screen shots of the data. I makes it much too hard to reproduce your situation. Instead include in the quesiton the CREATEs and INSERTs required to reproduce the test cases and also what queries you've tried and why the don't work. See mcve. I've written the minimum to demonstrate the proposed solution.

drop table if exists Table1;
drop table if exists Table2;

create table Table1(Name varchar(9), Record int); -- other columns omitted for simplicity
create table Table2(Name varchar(9), Record int);

insert Table1 values ('Joy', 1), ('Joy', 2);
insert Table2 values ('Joy', 0);

-- I've written this as a Common Table Expression (CTE). A sub-query would work just as well
;with Data as
    Name, Record, BlockNumber = 2   -- BlockNumber for Table2 is greater than that for Table1
from Table2  -- the order of queries within the CTE does not affect the output

union all

    Name, Record, BlockNumber = 1
from Table1
    Name, Record -- note that BlockNumber is not returned
from Data
order by
    Name,           -- the leading column of the sort so one person's rows all appear together
    BlockNumber,    -- will put Table2's row after Table1's.
    Record;         -- since all Table1's rows are in the same block they'll sort by Record
| improve this answer | |

I used max(record +1) to make the 0 always the last record.
Now, what I need is to compare row to the row above it and write record when the delDate is different. It should only apply to the same ID.
Here is my query:

            from Table2 (nolock)
            ID = T1.ID
            and Code = T1.Code
            ) Record
        from Table1 T1
        from Table2 T0
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.