July 23, 2013 at 7:59 am
Hello,
I need your help in the following scenario.
I have customer information in two tables, ProductA_Customer and ProductB_Customer.
I am trying to create a report that would include all Customers from ProductA_Customer
and customers from ProductB_Customer that have no record in ProductA_Customer.
The result needs to be formatted in the following table
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
We are using SQL Server 2005. I would appreciare your help
Thanks
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply