Mysql query to join three tables

inner-join, join, mysql, sql

Solution

Try this:

SELECT a.sales_id, d.bus_title, s.cat_id
FROM tbl_sales a
INNER JOIN tb_sales_category s ON a.sales_id = s.sales_id
INNER JOIN tbl_business      d ON a.bus_id   = d.bus_id
INNER JOIN tb_category       b ON s.cat_id   = b.cat_id

The idea is fairly simple, the first field in your new table `tb_sales_category` which is `sales_category_id` is working as a surrogate key, it has nothing to do with the relations between the two other tables. Then we come to the other two fields which are `sales_id`, `cat_id`, these what you should map to the other two sides of the relations.

You can't `Join tb_category b ON a.cat_id = b.cat_id` on the new schema becouse we no longer have `a.cat_id`, and here comes the new table `tb_sales_category` role, by inserting it with two binding sides, one with `INNER JOIN tb_category b ON s.cat_id = b.cat_id` and the other with `INNER JOIN tb_sales_category s ON a.sales_id = s.sales_id` we should be done.

Hope this makes sense.

Problem

Original source