Mysql query to join three tables
Solution 1
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.
Solution 2
I am not a great fan of inner joins so try this:
SELECT a.sales_id, a.cat_id, c.bus_title
FROM tb_sales_category a, tb_sales b, tbl_business c
WHERE a.sales_id = b.sales_id
AND b.bus_id = c.bus_id
Comments
-
Jimmy M about 4 years
I am using this query:
SELECT a.sales_id, d.bus_title, a.cat_id FROM tbl_sales a INNER JOIN tb_category b ON a.cat_id = b.cat_id INNER JOIN tbl_business d ON d.bus_id = a.bus_id
which produces this result:
sales_id | bus_title |cat_id ----------|----------------|------------ 1 | Business 1 | 6 2 | Business 12 | 12 3 | Business 123 | 25
I changed the field cat_id into a new table named
tb_sales_category
which contains the fieldssales_category_id
,sales_id
,cat_id
. How can I write the new query by joining this table too to, get the same result as above?I am kind of new to databases, need help. Thanks in advance
-
liquorvicar about 12 yearsWhy on earth don't you like INNER JOINs?
-
Jimmy M about 12 years@gimg1 Thanks, that serves the purpose
-
Jimmy M about 12 yearsalso thanks for the explanation, this will help me in my self learning process