Practise Different Join Queries | Noroff Front-end Development

Try these variations to understand how JOINs work:

Get all posts with their authors (no WHERE clause)

SELECT posts.title, posts.content, users.username
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Get only the title and author name

SELECT posts.title, users.username
FROM posts
INNER JOIN users ON posts.user_id = users.id;

Get posts with creation date and author

SELECT posts.title, posts.created_at, users.username
FROM posts
INNER JOIN users ON posts.user_id = users.id
ORDER BY posts.created_at DESC;

Count how many posts a user has written

SELECT COUNT(*) as post_count
FROM posts
INNER JOIN users ON posts.user_id = users.id
WHERE users.id = 4;

This query introduces two new concepts:

COUNT(*) - A function that counts the number of rows returned by the query.
AS post_count - Creates a column alias, giving the result column a custom name instead of the default “COUNT(*)”.

In this case, it counts how many posts the user with ID 4 has written by using a JOIN with a WHERE clause. The result will show a single number in a column labeled “post_count”.

Tags: