create table invoice_master ( InvoiceID varchar(10), ClientInfo ... ) ; create table line_items ( PK_item varchar(10), FK_InvoiceID varchar(10), LineItemInfo ... ) ;
My report-writer person has to render a report that aggregates invoices based on various combinations of line item codes. For example, for a given client, how many invoices included a line item for premium processing (one of the line item codes is premium processing) of an H1 visa (processing an H1 visa is another line item code); how many invoices for an H1 without premium processing; how many H1 extensions, with and without premium processing, etc. My report writer says (and I believe her because I don't know any better) that she must perform multiple outer self joins on the line_item table in order to create this report. She also says that while one can do a single outer join with MySQL, one cannot do multiple outer joins. Unfortunately, I don't know enough SQL, or MySQL, to argue the point with her. I do know enough about RDBMS design, however, to object strongly to her proposed solution, which is to add 10 columns to the invoice header table (there can only be 10 line items on any given invoice -- most, however, only have two or three line items). (BTW, is an "outer" join the same thing as a left join?) So, the question: is Ms. Report Writer right or wrong? Or, maybe the better question is, how can this be done?
Any and all help appreciated.
Randy
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
