Mysql query to join three tables

65,160

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
Share:
65,160
Jimmy M
Author by

Jimmy M

A newbie to PHP

Updated on April 21, 2020

Comments

  • Jimmy M
    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 fields sales_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
    liquorvicar about 12 years
    Why on earth don't you like INNER JOINs?
  • Jimmy M
    Jimmy M about 12 years
    @gimg1 Thanks, that serves the purpose
  • Jimmy M
    Jimmy M about 12 years
    also thanks for the explanation, this will help me in my self learning process