Combining fields in a 1 to many relationship
Posted: Mon Aug 23, 2010 11:28 pm
Hello,
This question may have been answered, and forgive me if it has. I am extracting data from an AS400 using DB2 SQL. I have two tables that I need information from. The first table is a straight file. When I reference the second table through a join, I get multiple lines with the exact same information except for the items in the second table. Tables are as follows...
Table1
Table2
Results
Desired results
Any help would be greatly appreciated.
This question may have been answered, and forgive me if it has. I am extracting data from an AS400 using DB2 SQL. I have two tables that I need information from. The first table is a straight file. When I reference the second table through a join, I get multiple lines with the exact same information except for the items in the second table. Tables are as follows...
Table1
Req_No Customer Job_Start Job_End
000001 John Doe 10-13-2009 10-14-2009
000002 Jane Doe 03-23-2007 03-25-2007
000003 Al Monroe 06-17-2004 06-19-2004
000001 John Doe 10-13-2009 10-14-2009
000002 Jane Doe 03-23-2007 03-25-2007
000003 Al Monroe 06-17-2004 06-19-2004
Table2
Req_No Line_No Descrip
000001 1 REPLACED CUSTOMER'S HYD PUMP
000001 2 WITH NEW PUMP. TESTING COMPLETE
000001 3 AS PER CONTRACT AGREEMENT
000002 1 TESTED METER. NO DEFECTS NOTED
000003 1 REMOVED AND REPLACED SAFETY
000003 2 SWITCH. TESTED AND OPERATIONAL.
000001 1 REPLACED CUSTOMER'S HYD PUMP
000001 2 WITH NEW PUMP. TESTING COMPLETE
000001 3 AS PER CONTRACT AGREEMENT
000002 1 TESTED METER. NO DEFECTS NOTED
000003 1 REMOVED AND REPLACED SAFETY
000003 2 SWITCH. TESTED AND OPERATIONAL.
Results
Req_No Customer Job_Start Job_End Descrip
000001 John Doe 10-13-2009 10-14-2009 REPLACED CUSTOMER'S HYD PUMP
000001 John Doe 10-13-2009 10-14-2009 WITH NEW PUMP. TESTING COMPLETE
000001 John Doe 10-13-2009 10-14-2009 AS PER CONTRACT AGREEMENT
000002 Jane Doe 03-23-2007 03-25-2007 TESTED METER. NO DEFECTS NOTED
000003 Al Monroe 06-17-2004 06-19-2004 REMOVED AND REPLACED SAFETY
000003 Al Monroe 06-17-2004 06-19-2004 SWITCH. TESTED AND OPERATIONAL.
000001 John Doe 10-13-2009 10-14-2009 REPLACED CUSTOMER'S HYD PUMP
000001 John Doe 10-13-2009 10-14-2009 WITH NEW PUMP. TESTING COMPLETE
000001 John Doe 10-13-2009 10-14-2009 AS PER CONTRACT AGREEMENT
000002 Jane Doe 03-23-2007 03-25-2007 TESTED METER. NO DEFECTS NOTED
000003 Al Monroe 06-17-2004 06-19-2004 REMOVED AND REPLACED SAFETY
000003 Al Monroe 06-17-2004 06-19-2004 SWITCH. TESTED AND OPERATIONAL.
Desired results
Req_No Customer Job_Start Job_End Descrip
000001 John Doe 10-13-2009 10-14-2009 REPLACED CUSTOMER'S HYD PUM WITH NEW PUMP. TESTING COMPLETE AS PER CONTRACT AGREEMENT
000002 Jane Doe 03-23-2007 03-25-2007 TESTED METER. NO DEFECTS NOTED
000003 Al Monroe 06-17-2004 06-19-2004 REMOVED AND REPLACED SAFETY SWITCH. TESTED AND OPERATIONAL.
000001 John Doe 10-13-2009 10-14-2009 REPLACED CUSTOMER'S HYD PUM WITH NEW PUMP. TESTING COMPLETE AS PER CONTRACT AGREEMENT
000002 Jane Doe 03-23-2007 03-25-2007 TESTED METER. NO DEFECTS NOTED
000003 Al Monroe 06-17-2004 06-19-2004 REMOVED AND REPLACED SAFETY SWITCH. TESTED AND OPERATIONAL.
Any help would be greatly appreciated.