A union that combines payment data from the same joined tables : Union « Set Operations « SQL Server / T-SQL Tutorial






4>
5> create table Billings (
6>     BankerID           INTEGER,
7>     BillingNumber      INTEGER,
8>     BillingDate        datetime,
9>     BillingTotal       INTEGER,
10>     TermsID            INTEGER,
11>     BillingDueDate     datetime ,
12>     PaymentTotal       INTEGER,
13>     CreditTotal        INTEGER
14>
15> );
16> GO
1>
2> INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);
3> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);
2> GO

(1 rows affected)
1>
2>
3>
4> create table Bankers(
5>    BankerID             Integer,
6>    BankerName           VARCHAR(20),
7>    BankerContactLName   VARCHAR(20),
8>    BankerContactFName   VARCHAR(20),
9>    BankerCity           VARCHAR(20),
10>    BankerState          VARCHAR(20),
11>    BankerZipCode        VARCHAR(20),
12>    BankerPhone          VARCHAR(20)
13> )
14> GO
1>
2> insert into Bankers values (1, 'ABC Inc.','Joe','Smith','Vancouver','BC','11111','111-111-1111');
3> GO

(1 rows affected)
1> insert into Bankers values (2, 'DEF Inc.','Red','Rice', 'New York', 'DE','22222','222-222-2222');
2> GO

(1 rows affected)
1> insert into Bankers values (3, 'HJI Inc.','Kit','Cat',  'Paris',    'CA','33333','333-333-3333');
2> GO

(1 rows affected)
1> insert into Bankers values (4, 'QWE Inc.','Git','Black','Regina',   'ER','44444','444-444-4444');
2> GO

(1 rows affected)
1> insert into Bankers values (5, 'RTY Inc.','Wil','Lee',  'Toronto',  'YU','55555','555-555-5555');
2> GO

(1 rows affected)
1> insert into Bankers values (6, 'YUI Inc.','Ted','Larry','Calgary',  'TY','66666','666-666-6666');
2> GO

(1 rows affected)
1> insert into Bankers values (7, 'OIP Inc.','Yam','Act',  'San Franc','FG','77777','777-777-7777');
2> GO

(1 rows affected)
1> insert into Bankers values (8, 'SAD Inc.','Hit','Eat',  'Orland',   'PO','88888','888-888-8888');
2> GO

(1 rows affected)
1> insert into Bankers values (9, 'DFG Inc.','Sad','Lee',  'Wisler',   'PL','99999','999-999-9999');
2> GO

(1 rows affected)
1> insert into Bankers values (0, 'GHJ Inc.','Bit','Lee',  'Ticker',   'MN','00000','000-000-0000');
2> GO

(1 rows affected)
1>
2>
3>     SELECT BillingNumber, BankerName, '33% Payment' AS PaymentType,
4>         BillingTotal AS Total, (BillingTotal * 0.333) AS Payment
5>     FROM Billings JOIN Bankers
6>         ON Billings.BankerID = Bankers.BankerID
7>     WHERE BillingTotal > 10000
8> UNION
9>     SELECT BillingNumber, BankerName, '50% Payment' AS PaymentType,
10>         BillingTotal AS Total, (BillingTotal * 0.5) AS Payment
11>     FROM Billings JOIN Bankers
12>         ON Billings.BankerID = Bankers.BankerID
13>     WHERE BillingTotal BETWEEN 500 AND 10000
14> UNION
15>     SELECT BillingNumber, BankerName, 'Full amount' AS PaymentType,
16>         BillingTotal AS Total, BillingTotal AS Payment
17>     FROM Billings JOIN Bankers
18>         ON Billings.BankerID = Bankers.BankerID
19>     WHERE BillingTotal < 500
20> ORDER BY PaymentType, BankerName, BillingNumber
21> GO
BillingNumber BankerName           PaymentType Total       Payment
------------- -------------------- ----------- ----------- ----------------
            1 ABC Inc.             Full amount         165          165.000
            2 DEF Inc.             Full amount         165          165.000
            9 DFG Inc.             Full amount         165          165.000
            0 GHJ Inc.             Full amount         165          165.000
            3 HJI Inc.             Full amount         165          165.000
            7 OIP Inc.             Full amount         165          165.000
            4 QWE Inc.             Full amount         165          165.000
            5 RTY Inc.             Full amount         165          165.000
            8 SAD Inc.             Full amount         165          165.000
            6 YUI Inc.             Full amount         165          165.000

(10 rows affected)
1>
2> drop table Billings
3> drop table Bankers;
4> GO
1>
2>








6.3.Union
6.3.1.Using Unions to Display Data from Multiple Queries
6.3.2.Listing the output from two identical tables.
6.3.3.UNION same type of columns from different tables
6.3.4.A union that combines information from the Billings table
6.3.5.A union that combines payment data from the same joined tables
6.3.6.OR operator can be used instead of the UNION operator, as the two equivalent examples.