Table 1
Category ID | Category Name
1 Lorem 1
2 Lorem 2
3 Lorem 3
Table 2
Post ID | Post Content | Category ID
1 Post 1 2
2 Post 2 2
3 Post 3 1
4 Post 4 3
Result
Categories:
Lorem 1
Lorem 2
Lorem 3
Category name will only display if there are post in that category. List will be displayed using li
Try this
SELECT DISTINCT table1.category_name
FROM table1
RIGHT JOIN table2 ON table1.category_id = table2.category_id;
Use Inner join with distinct caluse:
select distinct CategoryName as categories from table1 a inner join table2 b
on a.categoryid=b.categoryid
$query =
"SELECT t1.cat_name
FROM t1,t2
WHERE t1.id = t2.cat_id
GROUP BY t1.cat_name";
You should change names of tables/attributes and choose your religion, lowercase or uppercase for all, and camel or snakecase
Without getting too far into the differences of DISTINCT vs. GROUP BY, my preference in this case would be to use DISTINCT, since you are not using aggregates and you are projecting a specific column in your SELECT statement (See this post for further details: Is there any difference between GROUP BY and DISTINCT).
There are a couple of ways that you could approach this. Arguably using INNER JOIN is the most readable version (below), however if your style is typically to run lots of subqueries then the subquery version (further below) might appeal to you better:
INNER JOIN method:
SELECT DISTINCT t1.category_name AS categories
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.category_id = t2.category_id
ORDER BY 1
See SQL Fiddle for active example: http://sqlfiddle.com/#!9/51de19/5
Subquery Method:
SELECT t1.category_name AS categories
FROM table_1 t1
WHERE category_id IN(
SELECT DISTINCT category_id
FROM table_2 t2
)
ORDER BY 1
See SQL Fiddle for active example: http://sqlfiddle.com/#!9/51de19/6
Solution
SELECT DISTINCT Table_1.Category_Name FROM Table_1 LEFT JOIN Table_2 ON Table_1.Category_ID = Table_2.Category_ID
