basic join question
in [Programming SQL]
|
From: AussieRules on 19 Jun 2010 06:49 Hi, I have two tables, one being like an invoice header, and the other being like a detail table. The head table has one record for the invoice, but the detail table has many lines per invoice. Due to a bad design, the customer name for the invoice has ended up as a field in the detail table, and not the header table. I am now trying to get back some of the header information which works fine.... select invoice_date, Invoice_number, invoice_value from tbl_invoice.. what I want to do however is to include the customer name in this result set... select invoice_date, Invoice_number, customer_name, invoice_value from tbl_invoice, tbl_detail where tblinvoice.invoice_number = tbl_detail.invoice_number.. The problem is that this join results in a record retured for each invoice_detail line.... How can do this so that I just get back the single header record, but with the customer name... Thanks
From: Geoff Schaller on 19 Jun 2010 08:11 Join to the lines table a second time but TOP 1 instead. Geoff Schaller Software Objectives "AussieRules" <nospam(a)nospam.com> wrote in message news:uEB1X05DLHA.3732(a)TK2MSFTNGP02.phx.gbl: > Hi, > > I have two tables, one being like an invoice header, and the other being > like a detail table. The head table has one record for the invoice, but the > detail table has many lines per invoice. > > Due to a bad design, the customer name for the invoice has ended up as a > field in the detail table, and not the header table. > > I am now trying to get back some of the header information which works > fine.... > > select invoice_date, Invoice_number, invoice_value from tbl_invoice.. > > what I want to do however is to include the customer name in this result > set... > > select invoice_date, Invoice_number, customer_name, invoice_value from > tbl_invoice, tbl_detail where tblinvoice.invoice_number = > tbl_detail.invoice_number.. > > The problem is that this join results in a record retured for each > invoice_detail line.... > > How can do this so that I just get back the single header record, but with > the customer name... > > Thanks
From: Erland Sommarskog on 19 Jun 2010 16:47 AussieRules (nospam(a)nospam.com) writes: > I have two tables, one being like an invoice header, and the other > being like a detail table. The head table has one record for the > invoice, but the detail table has many lines per invoice. > > Due to a bad design, the customer name for the invoice has ended up as a > field in the detail table, and not the header table. > > I am now trying to get back some of the header information which works > fine.... > > select invoice_date, Invoice_number, invoice_value from tbl_invoice.. > > what I want to do however is to include the customer name in this result > set... > > select invoice_date, Invoice_number, customer_name, invoice_value from > tbl_invoice, tbl_detail where tblinvoice.invoice_number = > tbl_detail.invoice_number.. > > The problem is that this join results in a record retured for each > invoice_detail line.... > > How can do this so that I just get back the single header record, but with > the customer name... If there is a lineno (or similar), in the details table, you could restrict the join on that. Or you could do: join (SELECT invoice_number, MIN(customer_name) FROM details GROUP BY invoice_number) AS details ON ... Or you could use CROSS APPLY CROSS APPLY (SELECT TOP 1 d.customer_name FROM details d WHERE d.invoice_number = i.invoice_number) AS d And there is of course the interesting question what applies when there are different names on different lines... I think you should try to change that design. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Iain Sharp on 21 Jun 2010 07:32 On Sat, 19 Jun 2010 20:49:26 +1000, "AussieRules" <nospam(a)nospam.com> wrote: >Hi, > >I have two tables, one being like an invoice header, and the other being >like a detail table. The head table has one record for the invoice, but the >detail table has many lines per invoice. > >Due to a bad design, the customer name for the invoice has ended up as a >field in the detail table, and not the header table. > >I am now trying to get back some of the header information which works >fine.... > >select invoice_date, Invoice_number, invoice_value from tbl_invoice.. > >what I want to do however is to include the customer name in this result >set... > >select invoice_date, Invoice_number, customer_name, invoice_value from >tbl_invoice, tbl_detail where tblinvoice.invoice_number = >tbl_detail.invoice_number.. > >The problem is that this join results in a record retured for each >invoice_detail line.... > >How can do this so that I just get back the single header record, but with >the customer name... > >Thanks > select inv_field1, inv_field2, inv_field3, cust_name, inv_field4 from invoice left outer join (select inv_no,max(cust_name) as cust_name from invitem group by inv_no) as itemdata on invoice.inv_no = itemdata.inv_no Iain
|
Pages: 1 Prev: ddl not working in scrip Next: Machine Name change not being recognized by SQL |