MySQL query from two tables with multiple joining

Question

I've two tables, example - category & items.

 1. category table has two fields- cat_id, cat_name.
 2. items table has 4 fields field1, field2, field3, field4 which value
    is cat_id.

Now, I need to print cat_name instead of cat_id in each row.

How can I do that? What will be the MySQL query?

For more info please, have a look here- Url: http://smartmux.com/demo_roundflat/admin/ username: test password: test

First look at joins and try them if you feel any problem then ask here — M Khalid Junaid
– M Khalid Junaid, Commented Mar 6, 2014 at 10:12
Thanks to all for reply. I'm facing problem because every fields (field1, field2, field3, field4) data of my items table are coming from category table. Now, I'm confused how can I join them. — Biplob Hossain
– Biplob Hossain, Commented Mar 6, 2014 at 10:23

Harshal · Accepted Answer · 2014-03-06 10:13:26Z

1

You need to use Join and your query should be like this :

"select t1.*, t2.cat_name FROM table1 as t1 JOIN table2 as t2 on t1.cat_id=t2.cat_id";

answered Mar 6, 2014 at 10:13

Harshal

3,6329 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Biplob Hossain Over a year ago

Thanks for your reply. But I'm confused here "t1.cat_id=t2.cat_id". My t1 has 4 field related with t2 (t1.field1, t1.field2, t1.field3, t1.field4). Then, what should be the joining?

Harshal Over a year ago

NO that should be common field of both of your table and which is cat_id

SagarPPanchal · Accepted Answer · 2014-03-06 10:14:00Z

1

SELECT c.id as cat_name  from category as c , items as i where c.id=i.cat_id

answered Mar 6, 2014 at 10:14

SagarPPanchal

10.2k6 gold badges36 silver badges66 bronze badges

Comments

dkasipovic · Accepted Answer · 2014-03-06 10:20:55Z

0

If I understood right you can do something like this:

SELECT items.*, c1.cat_name, c2.cat_name, c3.cat_name, c4.cat_name
FROM items
LEFT JOIN category AS c1 ON c1.cat_id = items.field1
LEFT JOIN category AS c2 ON c2.cat_id = items.field1
LEFT JOIN category AS c3 ON c3.cat_id = items.field1
LEFT JOIN category AS c4 ON c4.cat_id = items.field1;

Let me know if this is what you needed.

answered Mar 6, 2014 at 10:20

dkasipovic

6,1301 gold badge23 silver badges27 bronze badges

1 Comment

Biplob Hossain Over a year ago

Thanks @D. Kasipovic, your idea worked with a little changes. SELECT items.*, c1.cat_name AS new1, c2.cat_name AS new2, c3.cat_name AS new3, c4.cat_name AS new4 FROM items LEFT JOIN category AS c1 ON c1.cat_id = items.field1 LEFT JOIN category AS c2 ON c2.cat_id = items.field2 LEFT JOIN category AS c3 ON c3.cat_id = items.field3 LEFT JOIN category AS c4 ON c4.cat_id = items.field4;

Abdul Manaf · Accepted Answer · 2014-03-06 10:13:55Z

0

SELECT 
    category.cat_name
FROM
    category
        JOIN
    items ON category.cat_id = items.field4

answered Mar 6, 2014 at 10:13

Abdul Manaf

4,8883 gold badges29 silver badges34 bronze badges

1 Comment

Biplob Hossain Over a year ago

Please, have a look here smartmux.com/demo_roundflat/admin username: test, password: test. Here exam1, exam2, exam3, exam4 are displaying category id. I need to print category name here. These 4 fields are related with category table.

Collectives™ on Stack Overflow

MySQL query from two tables with multiple joining

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related