Joining on Fields From 3 Tables
I have 3 tables in my database. Lets call them tables A, B, and C.
Table A has 4 fields: A1, A2, A3, and A4. A1 and A2 are tinyints, org reference codes. A3 is a number. And A4 is a date.
Table B has 4 fields: B1, B2, B3, and B4. B1 and B2 are tinyints, org reference codes. And B3 and B4 are dates.
Finally, Table C has 4 fields: C1, C2, C3, and C4. C1 and C2 are tinyints, org reference codes. C3 is a date. And C4 is a number.
In my batabase, these three tables are joined together in the following way:
Explanation: Table A contains my primary sales data, which is joined onto various other tables for various rules and other information. Table B contains some locational data, joined from the primary table based on the location ID (A1 and B1) and within a certain date range (A4 within B3 and B4). The locational data within B includes a category ID (B2). The location's category (B2), and the Primary's category (A2) are both used to join table C to retrieve a value needed to multiply the sales number (A3 * C4).
My current view has Table A joining a large number of tables to retrieve whatever information the report requires. Table A also contains a lot of data that isn't normally needed or joined on. So I would strongly prefer not to write a freehand SQL that performs the same function, as this would slow down any reports that use this view.
As near as I can tell, Yellowfin has no way of allowing me to join table C using "A2 = C1 and B2 = C2".
The best alternative solution I can think of is to write a freehand virtual table that performs the join between tables B and C. And then have table A join it appropriately. However, I believe this isn't a particularly efficient solution and side-steps Yellowfin's view optimizer. It could also become particularly problematic as B and C grow in size, joining a ridiculous number of unneeded rows or combinations of categories, dates, and locations that may never exist within A, slowing down queries and reports.