Thread: Join question
I have a database in a parts sales environment that I am having a little difficulty with a join query - trying to figure out which way to join things. I have a parts_invoice_header table, containing the header record for each invoice. I have a parts_invoice_lines table, containing the parts details for each invoice. I have a parts_invoice_sundries table, containing additional sundry charges (freight, delivery etc) on each invoice. For each record in the invoice_header table, there can be 0 or more records in either of the two other tables. I want to search for an invoice in the header file and get the details of all matching records from the lines and sundries. Assuming the lines and sundries tables both have the same column names, I should be able to end up with something like: table | header header line/sundries | column| Invoice number customer item amount line | 1111 abc 457ABC 10.00 sundry| 1111 abc FREIGHT 5.00 line | 1111 abc FGOIL 15.00 What would be the best method of joining to create a result-set such as this? TIA for any assistance, P. -- Paul Lambert Database Administrator AutoLedgers
This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROM lines AS l JOIN headers AS h UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROM sundries AS s JOIN headers AS h ORDER BY invoice_number, item *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail.
Whoops, I forgot the JOIN conditions! Fixed below -----Original Message----- From: Phillip Smith [mailto:phillip.smith@weatherbeeta.com.au] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROM lines AS l JOIN headers AS h ON l.invoice_number = h.invoice_number UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROM sundries AS s JOIN headers AS h ON s.invoice_number = h.invoice_number ORDER BY invoice_number, item *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail.
Phillip Smith wrote: > Whoops, I forgot the JOIN conditions! Fixed below > > -----Original Message----- > From: Phillip Smith [mailto:phillip.smith@weatherbeeta.com.au] > Sent: Friday, 27 July 2007 11:47 > To: 'pgsql-sql@postgresql.org' > Subject: RE: [SQL] Join question > > > This might give you a starting point if I understand you correctly... > > SELECT h.invoice_number, > h.customer, > l.item, > l.amount > FROM lines AS l > JOIN headers AS h ON l.invoice_number = h.invoice_number > UNION > SELECT h.invoice_number, > h.customer, > s.item, > s.amount > FROM sundries AS s > JOIN headers AS h ON s.invoice_number = h.invoice_number > ORDER BY invoice_number, item > Forgot all about union - I had two individual views, one for invoice+parts, one for invoice+sundries... didn't think of union to combine the two views together. Perfect solution, you've done it for me again Phillip, thanks. -- Paul Lambert Database Administrator AutoLedgers