CODEWITHSUNDEEP
phpsqlmysqljoin
CodeOverload
CodeOverload

Reputation: 48535

Querying 2 Tables in a single query

I have a table for posts and a table for categories, what i want is to select posts that are in a specific category. The problem is that the category is stored in another table and not in the posts table, here is the example:

posts

id   title       body
---------------------------
125  Some title  Blah blah

categories

postid  category
----------------
125     politic

I want in single query to fetch posts in the politic category by example, what to do?

Upvotes: 2

Views: 165

Answers (3)

jon_darkstar
jon_darkstar

Reputation: 16788

You need to join the two tables in your query

SELECT *
FROM posts P
INNER JOIN categories C on C.post_id = P.id
WHERE C.category = 'politic'

You can intuitively (this is NOT exactly a technically correct explanation) think of this join as appending the category field to a row in posts with the shared id (this is the 'on C.post_id = P.id'). The WHERE clause indicates that you want only those rows where category is 'politic'.

Inner join is one of several join types. Left join in particularly is another common one, and its difference in this situation is that rows of post without a match in categories would still be listed, but with nulls for the fields from categories. (With inner join such rows would not remain)

http://en.wikipedia.org/wiki/Join_%28SQL%29

Upvotes: 0

pinkfloydx33
pinkfloydx33

Reputation: 12799

select p.* 
from posts p 
inner join categories c 
       on p.id = c.postid 
where 
     c.category = 'politic'

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332731

Use:

SELECT p.id,
       p.title, 
       p.body
  FROM POSTS p
  JOIN CATEGORIES c ON c.postid = p.id
 WHERE c.category = 'politic'

The issue I have with your CATEGORIES table is that storing the category value as a string means the data isn't normalized - you should instead have a CATEGORY table:

CATEGORY

  • category_id (primary key, auto_increment)
  • category_description

...and use the category_id value in the CATEGORIES table:

CATEGORIES

  • category_id (primary key, foreign key to CATEGORY.category_id)
  • post_id (primary key, foreign key to POSTS.postid)

Upvotes: 4

Related Questions