1

I have two data sources, one that tracks products and one that tracks financial information. The two do not have a common field, but they both have a common field with our billing system. I want to use our billing system data as a bridge table between my two sources so that I can connect product data to financial data in one analysis without having to do a messy and slow blend in our viz tool (for example – using case logic to make product.client = finance.account…inconsistent naming conventions are the worst). I am basically using billing as a lookup table to first join product to billing and then join finance to my new join…I think.

I want to create three mysql tables with fields along the lines of the lettered items. 1. Product a. client b. prod_id c. size d. metric 2. Finance a. account b. fine_id c. amount d. country 3. Billing a. account b. prod_id c. fine_id

I am using billing as a lookup table to first join product to billing so that I can get all my product fields plus account and fine_id. I then want to join finance to that first join. I can make it work in excel, but the scale of data we are working with once (if) this tool goes live is too big for excel, thus the need for MySQL. Am I correct that I can achieve this type of join on a join w/ MySQL? I would really appreciate any syntax guidance on joining my finance table to the join below. Thanks!

Edit to post - syntax for joining, what is the proper order of my joins? the error that I get is simply that there is an error with my syntax.I am using the below. I have reordered it a few times but I am still missing some conceptual piece of how to do this. I want to 1. join product to billing (returning rows even where there isn't a match) 2. join finance to the product/billing outer join so that I can see my product and finance data in the same table. thanks for all the help!

select *
from
(select *
    from
    (select * from 
    lookup.product) as product_join
    left outer join
    (select *
    from lookup.billing) as billing_join
    on product_join.product_id = billing_join.product_id) as left # left join of billing to product
left outer join
    (select *
    from left) as left_join
    left outer join
    (select * from lookup.finance) as finance_join
    on left.finance_id = financef_join.finance_id) # joining of finance data to the left join of my product/billing
Nick
  • 131
  • 1
  • 2
  • 11
  • You could definitely do that with mysql, take a look at http://stackoverflow.com/questions/10862201/how-to-join-multiple-tables-including-lookup-table-and-return-data-in-rows – RP- Aug 17 '15 at 21:38

1 Answers1

1

Your task/question: "I want to create three mysql tables with fields along the lines of the lettered items.

  1. Product a. client b. prod_id c. size d. metric
  2. Finance a. account b. fine_id c. amount d. country
  3. Billing a. account b. prod_id c. fine_id

I am using billing as a lookup table to first join product to billing so that I can get all my product fields plus account and fine_id. I then ..."

That's possible and easy with a join:

SELECT * From product, billing, finance
WHERE  billing.account = finance.account
AND billing.fine_id = finance.fine_id
AND billing.prod_id = product.prod_id

On respect of your comment, you can use the left outer join like this, if a correspondent billing data does not exist and you want all products in the results:

SELECT * from product
LEFT outer join billing
ON product.prod_id = billing.prod_id 
LEFT outer join finance
ON billing.account = finance.account
AND billing.fine_id = finance.fine_id;

You don't have to put another "select *" around a join. You can just join the tables together like this.

dev.null
  • 538
  • 2
  • 7
  • Agreed, but I would use an explicit left join so that inconsistent billing information shows up and (hopefully) throws an error – Kaii Aug 17 '15 at 22:15
  • You cannot get an error but lines with empty values for billing, if the values are missing. If billing is not complete you also didn't get finance, cause billing is your joining table. – dev.null Aug 17 '15 at 22:20
  • "You cannot get an error" - you know what error handling is, right? `if (billing_information == NULL) error_condition_met();` – Kaii Aug 18 '15 at 07:33
  • Yes of course, I meant, the idea of the outer join is to get results with (here:) product datas and the billing datas are null, as you mentioned. – dev.null Aug 18 '15 at 09:57
  • edited the post to reflect my join issue. I am still missing something with my syntax. thanks for all the help this is very informative for a novice MySQLer – Nick Aug 18 '15 at 14:10
  • Edited my answer, too, to complete the outer join statement. – dev.null Aug 18 '15 at 14:14