How to Select Category Name on the Basis of Id

Question

I am Using PHP, MYSQL. I have two tables

Table posts with following fields

id,
title,
body,
cat_id

Table categories with following fields

cat_id,
cat_name

Suppose, I have Following Data in posts table

id = 1
title = "This is Test Post"
body = "This is the Body of Test Pots"
cat_id = 3

and in categories table

cat_id = 3
cat_name = "Mobiles"

If I Use the Following SQL Statement

"SELECT * FROM posts WHERE id=1";

It will give me following output

id = 1
title = "This is Test Post"
body = "This is the Body of Test Pots"
cat_id = 3

But I Want the Following Output

id = 1
title = "This is Test Post"
body = "This is the Body of Test Pots"
cat_id = Mobiles

How can I get the above mentioned output.

Note: I know some kind of JOIN is used in this kind of situation, But I don't know how to use it. One More thing, Is it possible to get my desired output without using any JOIN, because I heard that JOINs Effect the Efficiency. If JOIN is necessary Please tell me the most efficient Way to get my desired output.

Answer 1

SELECT * FROM posts JOIN categories USING (cat_id) WHERE posts.id = 1

It's possible to achieve the same using a correlated subquery instead (however this is likely to be less efficient than a join):

SELECT *, (SELECT cat_name FROM categories WHERE cat_id = posts.cat_id)
FROM   posts
WHERE  id = 1

Answer 2

You have to use join querty to join posts and categories in order to get informations from both tables. So you try with the following query

SELECT t1.*,t2.cat_name FROM posts as t1 join categories as t2 on t1.cat_id = t2.cat_id  WHERE t1.id=1

Answer 3

Use join query try this

SELECT id, title, body, cat_name FROM posts 
join categories on posts.cat_id = categories.cat_id  WHERE id=1

Answer 4

SELECT post.id, post.title, post.body, cat.cat_name from posts inner join categories cat on cat.cat_id = post.cat_id and post.id = 1

Answer 5

I think you have to learn JOINS first read this articles

• JOINS
• JOINS VS SUB-QUERY

now about your question checkout the

SQL FIDDLE

SELECT * FROM posts As p
JOIN categories AS c ON c.id = p.cat_id
WHERE p.id = 1

now its your choice whether to use Joins or Sub-Queries both have its pros and cons thus select as per your requirement.

hope this will help you ...!