Home

Unions of Records

 

Uniting Some Records

 

Introduction

Consider the following tables:

USE Exercise1;
GO

DROP TABLE HumanResources.Employees;
GO
DROP TABLE HumanResources.Contractors;
GO
CREATE TABLE HumanResources.Employees
(
    EmployeeNumber nchar(9),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    [Status] nvarchar(20) default N'Employee'
);
GO
CREATE TABLE HumanResources.Contractors
(
    ContractorCode nchar(7),
    Name1 nvarchar(20),
    Name2 nvarchar(20),
    Wage decimal(6, 2),
    [Type] nvarchar(20) default N'Contractor'
);
GO

INSERT INTO HumanResources.Employees(EmployeeNumber,
		FirstName, LastName, HourlySalary)
VALUES(N'2930-4708', N'John', N'Franks', 20.05),
      (N'8274-9571', N'Peter', N'Sonnens', 10.65),
      (N'6359-8079', N'Leslie', N'Aronson', 15.88);
GO
INSERT INTO HumanResources.Contractors(ContractorCode, Name1, Name2, Wage)
VALUES(N'350-809', N'Mary', N'Shamberg', 14.20),
      (N'286-606', N'Chryssa', N'Lurie', 20.26);
GO

Sometimes, either for the sake of comparing records or for preparing them for any operation, you may want to display the records of more than one table. To support the ability to select records of various tables and show them together, you use the UNION operator. The basic formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION
SELECT WhatField(s) FROM AnotherTable;

 There are rules you must follow:

  • Both tables must have the same number of columns
  • The sequence of data types of the columns in each table must be the same. For example, if the column in one table is string-based, the corresponding column, by order, in the other table must also be string-based
  • The data types in the order of the columns of both tables must be compatible. For example, if the first column of one table uses an integer-based data type, the first column of the other table must also have an integer-based data type that can be reconciled with the corresponding column of the other table

The columns don't have to have the same name. Here is an example of getting a union of all records from both tables:

SELECT * FROM HumanResources.Employees
UNION
SELECT * FROM HumanResources.Contractors;
GO

This would produce:

Uniting ALL Records

If you use the above formula, the records of all SELECTed tables would be included in the result. As an alternative, you can set a condition for the table(s) to exclude some records. You can set a condition on only one table, some tables, or all tables, but each table must have its own condition. Here is an exmple that unites some records of the above employees whose name end with s and the contractors who earn more than 20.00:

SELECT * FROM HumanResources.Employees WHERE LastName LIKE N'%s'
UNION
SELECT * FROM HumanResources.Contractors WHERE Wage >= 20.00;
GO

This would produce:

Uniting ALL Records

Practical LearningPractical Learning: Uniting the Records

  1. To see the records of tenants from Rockville and Alexandria previous databases, type the following code:
    USE LPM2;
    GO
    SELECT * FROM Rentals.Tenants;
    GO
    SELECT * FROM Rentals.Customers;
    GO
  2. To execute, press F5
    UNION
    
    
    
  3. Click inside the Query Editor and press Ctrl + A
  4. To unite the records of the tenants, type the following code:
    USE LPM2;
    GO
    SELECT t.TenantCode, t.FirstName, t.LastName, 
           t.MaritalStatus, t.PhoneNumber, t.EmailAddress
    FROM Rentals.Tenants t
    UNION
    SELECT c.AccountNumber, c.FirstName, c.LastName, c.[Status], c.Phone, c.Email
    FROM Rentals.Customers c;
    GO
  5. Press F5 to execute. This would produce:

    UNION
  6. Click inside the Query Editor and press Ctrl + A
  7. If you want, you can specify the captions you want. Change the code as follows:
    USE LPM2;
    GO
    SELECT r.RegistrationNumber [Regist #],
           r.RegistrationDate   [Regist Date],
           r.TenantCode         [For Account #],
           r.PropertyNumber     [For Prop #],
           r.RentStartDate      [Rent Start On]
    FROM Rentals.Registrations r
    UNION
    SELECT a.AllocationID, a.DateAllocated, a.AccountNumber,
           a.ApartmentNumber, a.DateOccupiedFrom
    FROM Rentals.Allocations a;
    GO
  8. Press F5 to execute

    Uniting Records

  9. Notice that you get 14 records.
    Click inside the Query Editor
  10. Press Ctrl + A and press Delete

Uniting ALL Records

By default, when UNION is used, the database engine arranges the records based on the first column. This means that if the first column is number-based, the records would be sorted in increment based on that column. If the first column is character-based, the list would be given with that column in alphabetical order. As an alternative, you can ask the database engine to include the records as they are made available, that is, the records of the first table, followed by those of the second table, and so on. To give this instruction, add the ALL keyword after UNION. The formula to follow is:

SELECT WhatField(s) FROM OneTable
UNION ALL
SELECT WhatField(s) FROM AnotherTable;

 Here is an example:

SELECT * FROM HumanResources.Employees
UNION ALL
SELECT * FROM HumanResources.Contractors;
GO

This would produce:

Uniting ALL Records

Practical LearningPractical Learning: Uniting ALL Records

  1. To unite all records, add the ALL keyword to the right of UNION:
    USE LPM2;
    GO
    SELECT r.RegistrationNumber [Regist #],
           r.RegistrationDate   [Regist Date],
           r.TenantCode         [For Account #],
           r.PropertyNumber     [For Prop #],
           r.RentStartDate      [Rent Start On]
    FROM Rentals.Registrations r
    UNION ALL
    SELECT a.AllocationID, a.DateAllocated, a.AccountNumber,
           a.ApartmentNumber, a.DateOccupiedFrom
    FROM Rentals.Allocations a;
    GO
  2. Press F5 to execute

    Uniting Records

  3. Click inside the Query Editor, press Ctrl + A, and press Delete
 
 
 

Copying From Many Tables

You can use the ability to copy records in order to get records from two or more tables and add them to a another table.

The formula to follow is:

INSERT INTO TableName
SELECT WhatField(s) FROM OneTable
UNION [ALL]
SELECT WhatField(s) FROM AnotherTable;

Here is an example:

USE LPM1;
GO

INSERT INTO Listing.Apartments
SELECT props.PropertyNumber,
       props.[Address],
       props.UnitNumber,
       props.City,
       props.[State],
       props.ZIPCode,
       props.Bedrooms,
       props.Bathrooms,
       props.MonthlyRate,
       props.SecurityDeposit
FROM Listing.Properties props
UNION
SELECT condos.CondoCode,
       condos.[Address],
       condos.CondoNbr,
       condos.City,
       condos.[State],
       condos.PostalCode,
       condos.Beds,
       condos.Baths,
       condos.Rent,
       condos.SecDepot
FROM Listing.Condominiums condos
UNION
SELECT alex.ApartmentCode,
       alex.[Address],
       alex.ApartNbr,
       alex.City,
       alex.[State],
       alex.ZIP,
       alex.Bedrooms,
       alex.Bathrooms,
       alex.MonthRent,
       alex.InitialDeposit
FROM Listing.AlexandriaApartments alex;
GO

Here is an example that displays the results

USE LPM1;
GO

SELECT aparts.PropertyNumber  N'Prop #',
       aparts.[Address],
       aparts.UnitNumber      N'Apart #',
       aparts.City,
       aparts.[State],
       aparts.ZIPCode         N'ZIP Code',
       aparts.Bedrooms        Beds,
       aparts.Bathrooms       Baths,
       aparts.MonthlyRate     Rent,
       aparts.SecurityDeposit Deposit
FROM Listing.Apartments aparts;
GO
ss F5
UNION


Practical LearningPractical Learning: Updating the Database

  1. Type the following code:
    USE LPM2;
    GO
    /* Change the property number where a record from Alexandria has
       the name property number as an apartment from Rockville */
    UPDATE Listing.Properties
    SET    PropertyNumber = N'3840-0002'
    WHERE (PropertyNumber = N'3840-8262') AND (City = N'Alexandria');
    GO
    UPDATE Listing.Properties
    SET    PropertyNumber = N'6200-0008'
    WHERE (PropertyNumber = N'6200-8048') AND (City = N'Alexandria');
    GO
    UPDATE Listing.Properties
    SET    PropertyNumber = N'3840-0003'
    WHERE (PropertyNumber = N'3840-0683') AND (City = N'Alexandria');
    GO
    UPDATE Listing.Properties
    SET    PropertyNumber = N'6286-0008'
    WHERE (PropertyNumber = N'6286-6408') AND (City = N'Alexandria');
    GO
    -- Harmonize the security deposits based on the rate of each house
    UPDATE Listing.Properties
    SET    SecurityDeposit =  500 WHERE MonthlyRate >= 800;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit =  600 WHERE MonthlyRate >= 900;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit =  700 WHERE MonthlyRate >= 1000;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit =  800 WHERE MonthlyRate >= 1200;
    GO
    UPDATE Listing.Properties
    SET    SecurityDeposit = 1000 WHERE MonthlyRate >= 1500;
    GO
    -- Add to the Rockville table of tenants the missing columns from the Alexandria table of customers
    ALTER TABLE Rentals.Tenants
    ADD DateCreated date;
    GO
    ALTER TABLE Rentals.Tenants
    ADD DateCreated date;
    GO
    ALTER TABLE Rentals.Tenants
    ADD EmergencyName nvarchar(40);
    GO
    ALTER TABLE Rentals.Tenants
    ADD EmergencyPhone nvarchar(20);
    GO
    /* Change the tenants account numbers from Alexandria
       where they are the same as tenants in Rockville */
    UPDATE Rentals.Customers
    SET    AccountNumber = N'292000'
    WHERE  AccountNumber = N'292475';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'293000'
    WHERE  AccountNumber = N'293759';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'295000'
    WHERE  AccountNumber = N'295804';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'524000'
    WHERE  AccountNumber = N'524794';
    GO
    UPDATE Rentals.Customers
    SET    AccountNumber = N'824000'
    WHERE  AccountNumber = N'824857';
    GO
    -- Copy the Alexandria tenants to those in Rockville
    INSERT INTO Rentals(TenantCode, DateCreated, FirstName, LastName,
                        MaritalStatus, NumberOfChildren, PhoneNumber,
    					EmailAddress, EmergencyName, EmergencyPhone)
    SELECT AccountNumber, DateCreated, FirstName, LastName, [Status],
           Children, Phone, Email, EmergencyName, EmergencyPhone
    FROM Rentals.Customers;
    GO
    /* Change the records of the Alexandria registrations
       based on the properties numbers and the accounts numbers
       that were changed earlier */
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'3840-0002', AccountNumber = N'295000'
    WHERE  AllocationID = 2;
    GO
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'6200-0008', AccountNumber = N'824000'
    WHERE  AllocationID = 5;
    GO
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'3840-0003', AccountNumber = N'292000'
    WHERE  AllocationID = 7;
    GO
    UPDATE Rentals.Allocations
    SET    ApartmentNumber = N'6286-0008', AccountNumber = N'293000'
    WHERE  AllocationID = 9;
    GO
    -- Create a new Registrations table to use an identity column
    CREATE TABLE Rentals.Agreements
    (
    	AgreementID      int identity(1, 1) not null,
    	RegistrationDate Date,
    	EmployeeNumber   int, -- Processed By
    	TenantCode       nvarchar(10), -- Processed For
    	PropertyNumber   nvarchar(10) not null,
    	RentStartDate    date
    );
    GO
    -- Add the Rockville regustrations to this table
    INSERT INTO  Rentals.Agreements(RegistrationDate, EmployeeNumber, 
                                    TenantCode, PropertyNumber, RentStartDate)
    SELECT RegistrationDate, EmployeeNumber, TenantCode, PropertyNumber, RentStartDate
    FROM Rentals.Registrations
    GO
    -- Then add the Alxandria registrations to the new table
    INSERT INTO Rentals.Agreements(RegistrationDate, TenantCode, PropertyNumber, RentStartDate)
    SELECT DateAllocated, AccountNumber, ApartmentNumber, DateOccupiedFrom)
    FROM Rentals.Allocations;
    GO
    /* Change the registrations numbers of the Alexandria tenants based on
       the new identity numbers of the Agreements table */
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 11
    WHERE  PaymentForAllocationNumber =  2;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 12
    WHERE  PaymentForAllocationNumber =  5;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 13
    WHERE  PaymentForAllocationNumber =  7;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 14
    WHERE  PaymentForAllocationNumber =  9;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 15
    WHERE  PaymentForAllocationNumber = 12;
    GO
    UPDATE Rentals.Receipts
    SET    PaymentForAllocationNumber = 16
    WHERE  PaymentForAllocationNumber = 15;
    GO
    -- Add the Alexandria payments to the Roclville's
    INSERT INTO Rentals.Payments(PaymentDate, EmployeeNumber, RegistrationNumber, AmountPaid, Notes)
    SELECT PaymentAmt, PaymentForAllocationNumber, DateReceiptMade, ReceiptDescription
    FROM Rentals.Receipts;
    GO
    
    
  2. To execute, on the main menu, click Query -> Execute
  3. Close Microsoft SQL Server
  4. When asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2009-2013 FunctionX Next