July 23, 2013 at 8:05 am
It will be good for us to solve your problem if you provide some more details like table structure, sample data and resultant output....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 23, 2013 at 8:21 am
Here is the table structure,
CREATE TABLE ProductA_Customer
(
Cust_No_A char(13) NOT NULL,
Cust_Name_A varchar(60) NULL,
Cust_Address_A varchar(60) NULL,
Cust_City_A varchar(20) NULL,
Cust_State_A char(2) NULL,
Cust_Zip_A char(10) NULL
)
CREATE UNIQUE CLUSTERED INDEX XPKProductA_Customer ON ProductA_Customer
(
Cust_No_A
)
CREATE TABLE ProductB_Customer
(
Cust_No_B char(13) NOT NULL,
Cust_Name_B varchar(60) NULL,
Cust_Address_B varchar(60) NULL,
Cust_City_B varchar(20) NULL,
Cust_State_B char(2) NULL,
Cust_Zip_B char(10) NULL
)
CREATE UNIQUE CLUSTERED INDEX XPKProductB_Customer ON ProductB_Customer
(
Cust_No_B
)
The report needs to be in the following format,
Cust_No_A
Cust_Name_A
Cust_Address_A
Cust_City_A
Cust_State_A
Cust_Zip_A
Cust_No_B
Cust_Name_B
Cust_Address_B
Cust_City_B
Cust_State_B
Cust_Zip_B
Thanks
July 23, 2013 at 11:52 pm
You need to follow this article for posting any question:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 24, 2013 at 7:38 am
-----------------------------
-- The following returns what you asked for (but may not be what you want):
-----------------------------
SELECT Cust_No_A
,Cust_Name_A
,Cust_Address_A
,Cust_City_A
,Cust_State_A
,Cust_Zip_A
,NULL AS Cust_No_B
,NULL AS Cust_Name_B
,NULL AS Cust_Address_B
,NULL AS Cust_City_B
,NULL AS Cust_State_B
,NULL AS Cust_Zip_B
FROM ProductA_Customer
UNION ALL
SELECT Cust_No_A
,Cust_Name_A
,Cust_Address_A
,Cust_City_A
,Cust_State_A
,Cust_Zip_A
,Cust_No_B
,Cust_Name_B
,Cust_Address_B
,Cust_City_B
,Cust_State_B
,Cust_Zip_B
FROM ProductB_Customer
LEFT JOIN ProductA_Customer
ON Cust_No_B = Cust_No_A
WHERE Cust_No_A IS NULL
-----------------------------
--The following would be a more typical recordset to return:
-----------------------------
WITH Cust_Unique(Cust_No, Cust_Name, Cust_Address, Cust_City, Cust_State, Cust_Zip, Table_Source)
AS (
SELECT Cust_No_A
,Cust_Name_A
,Cust_Address_A
,Cust_City_A
,Cust_State_A
,Cust_Zip_A
,'A'
FROM ProductA_Customer
UNION ALL
SELECT Cust_No_B
,Cust_Name_B
,Cust_Address_B
,Cust_City_B
,Cust_State_B
,Cust_Zip_B
,'B'
FROM ProductB_Customer
LEFT JOIN ProductA_Customer
ON Cust_No_B = Cust_No_A
WHERE Cust_No_A IS NULL
)
SELECT *
FROM Cust_Unique
ORDER BY Cust_No
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply