Randy,
>This isn't going to be a very good question because I don't really
>understand what I'm being asked... I have an invoicing database with two
>tables: An invoice header table; and a line items table. The line items
>table includes a column for the foreign key to the invoice header, a
>column for the services provided code, then unit cost, extended cost,
>description, etc., like so:
>create table invoice_master (
> InvoiceID varchar(10),
> ClientInfo ...
)
>;
>create table line_items (
> PK_item varchar(10),
> FK_InvoiceID varchar(10),
> LineItemInfo ...
> )
>;
First, you'll get better performance (and other benefits too) if you make
the primary keys INTs.
>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?
A left join is a left outer join. 'Outer' is syntactically optional--the
point is that the join is not inner, because in contrast to an inner join,
a left [outer] join lists all rows from the left table in the join, with
corresponding column values from the right table in the join, and nulls
for left-table keys that are not found in the right table. Of course these
right table rows may be not found because they are not there, or
because your join filters them out.
An inner join between master and items tables gives you master rows
for which there is an item row with some attribute set. If you need counts
or item lists for multiple conditions, you may be able to get by with 'GROUP
BY', but once the conditions start to multiply you will probably need either
multiple passes, or (better for performance) a temp result table in memory
and one pass through the line_items table in application code, updating your
temp result table as you go, then sending the result table to the report
generator.
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
