CODEWITHSUNDEEP
t-sql
BBauer42
BBauer42

Reputation: 3657

Union Select Distinct syntax?

I have a huge table that contains both shipping address information and billing address information. I can get unique shipping and billing addresses in two separate tables with the following:

SELECT DISTINCT ShipToName, ShipToAddress1, ShipToAddress2, ShipToAddress3, ShipToCity, ShipToZipCode
FROM Orders
ORDER BY Orders.ShipToName

SELECT DISTINCT BillToName, BillToAddress1, BillToAddress2, BillToAddress3, BillToCity, BillToZipCode
FROM Orders
ORDER BY Orders.BillToName

How can I get the distinct intersection of the two? I am unsure of the syntax.

Upvotes: 1

Views: 1079

Answers (3)

Taryn
Taryn

Reputation: 247650

You can join both sets on all fields and this will return the records that match:

SELECT *
FROM Orders o1
INNER JOIN Orders o2
    ON o1.ShipToName = o2.BillToName
    AND o1.ShipToAddress1 = o2.BillToAddress1
    AND o1.ShipToAddress2 = o2.BillToAddress2
    AND o1.ShipToAddress3 = o2.BillToAddress3 
    AND o1.ShipToCity = o2.BillToCity
    AND o1.ShipToZipCode = o2.BillToZipCode

Or you should be able to use INTERSECT:

SELECT ShipToName, ShipToAddress1, ShipToAddress2, ShipToAddress3, ShipToCity, ShipToZipCode
FROM Orders
INTERSECT
SELECT BillToName, BillToAddress1, BillToAddress2, BillToAddress3, BillToCity, BillToZipCode
FROM Orders

Or even a UNION query (UNION removes duplicates between two sets of data):

SELECT ShipToName, ShipToAddress1, ShipToAddress2, ShipToAddress3, ShipToCity, ShipToZipCode
FROM Orders
UNION 
SELECT BillToName, BillToAddress1, BillToAddress2, BillToAddress3, BillToCity, BillToZipCode
FROM Orders

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

You say "Intersection" but you accepted the Union answer so I guess you just want the UNION DISTINCT. No need for derived tables and the three DISTINCT. You can use the simple:

SELECT 
    ShipToName     AS Name, 
    ShipToAddress1 AS Address1, 
    ShipToAddress2 AS Address2, 
    ShipToAddress3 AS Address3, 
    ShipToCity     AS City, 
    ShipToZipCode  AS ZipCode
FROM 
    Orders

UNION                               --- UNION means UNION DISTINCT

SELECT  
    BillToName 
    BillToAddress1, 
    BillToAddress2,
    BillToAddress3,
    BillToCity,
    BillToZipCode
FROM 
    Orders 

ORDER BY
    Name ;

Upvotes: 3

Beth
Beth

Reputation: 9607

something like this?

SELECT DISTINCT
    toname, addr1,  addr2,  addr3,  city,   zip
FROM
    (SELECT DISTINCT 
        ShipToName AS toName, 
        ShipToAddress1 AS addr1, 
        ShipToAddress2 AS addr2, 
        ShipToAddress3 AS addr3, 
        ShipToCity AS city, 
        ShipToZipCode AS zip
    FROM 
        Orders

    UNION ALL

    SELECT DISTINCT 
    BillToName AS toName, 
    BillToAddress1 AS addr1, 
    BillToAddress2 AS addr2, 
    BillToAddress3 AS addr3, 
    BillToCity AS city, 
    BillToZipCode AS zip
    FROM 
        Orders) o
ORDER BY ToName

Upvotes: 3

Related Questions