CODEWITHSUNDEEP
phpmysqlsqldatabase
Tahir Aziz
Tahir Aziz

Reputation: 67

How to Select Category Name on the Basis of Id

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.

Upvotes: 1

Views: 2422

Answers (5)

Ashish Jagtap
Ashish Jagtap

Reputation: 2819

I think you have to learn JOINS first read this articles

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 ...!

Upvotes: 0

Prafful Garg
Prafful Garg

Reputation: 214

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

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

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

Upvotes: 0

웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

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

Upvotes: 0

eggyal
eggyal

Reputation: 125855

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

Upvotes: 2

Related Questions