Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Question Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 10:16 AM
Points: 25, Visits: 83
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
Post #1476588
Posted Tuesday, July 23, 2013 8:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1476595
Posted Tuesday, July 23, 2013 8:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 10:16 AM
Points: 25, Visits: 83
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








Post #1476605
Posted Tuesday, July 23, 2013 11:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1476879
Posted Wednesday, July 24, 2013 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:14 AM
Points: 101, Visits: 322
-----------------------------
-- 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
Post #1477059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse