CODEWITHSUNDEEP
mysqljoin
SunAmonRa
SunAmonRa

Reputation: 25

MySQL query to get headlines from subcategories of a given parent

I have a database structure like this

CATEGORIES
ID | parent_id | name
1  | 0         | sports
2  | 1         | football
3  | 1         | tennis
4  | 0         | activities
5  | 4         | hiking
etc.


NEWS
ID | category_id | title              | date       | etc
1  | 2           | Football is great  | 2012-12-21 | 
2  | 3           | Tennis is healthy  | 2012-11-13 |
3  | 5           | Go on hiking       | 2013-11-15 |

Now, when I want to get the headlines(titles) of the subcategories of "sports", what is the best way to achieve this?

I was thinking of doing multiple queries (3 in this case).

First, get all childs of "Sports"

SELECT * FROM categories WHERE parent_id = 1

Then, for each result do a query to get the headlines from each subcategory.

SELECT * FROM news WHERE category_id = (result from query above)

In this example, 3 queries will be executed. (One to get all subcategories. There are two subcategories in this case, so two queries will be executed to the news table. That makes a total of 3.)

Or is there a smarter (more efficient) way to do this? Perhaps 1 query?

Upvotes: 0

Views: 217

Answers (1)

ajtrichards
ajtrichards

Reputation: 30565

You should be able to do this with one query using INNER JOIN

SELECT NEWS.*, CATEGORIES.name 
FROM NEWS 
INNER JOIN CATEGORIES ON NEWS.category_id = CATEGORIES.ID

You can also write the query using a LEFT JOIN. This will allow you to list all categories including those with no news articles.

SELECT categories.name as Category_Name, news.*
FROM categories  
LEFT JOIN news ON news.category_id = categories.ID

I've put this in a SQL Fiddle to help demonstrate: http://sqlfiddle.com/#!2/9c1b3/6

Upvotes: 1

Related Questions