One-to-many relationship

Hi,

I’m struggling to get my head around how to structure a query for a profile page on my site.

A basic version of the query is as follows -


SELECT `users`.`uid`
, `users`.`name`
, `users`.`email`
, `user_groups`.`name` AS `group_name`
, `user_groups`.`premium`
FROM `users`
INNER
  JOIN user_groups
	ON user_groups.id = users.user_group_id								   
WHERE `users`.`uid` = '{$uid}'
LIMIT 1;

This is working fine for now but I want to add a posts section on each profile. The posts table in basic form consists of -

  • pid (post id)
  • name
  • content
  • uid (user id)

So I need a query that will loop through the posts table and get every post the user has made (which can be 0). Is it viable to use just 1 query here still or do I just need to do a simple query using "WHERE uid = ‘{$uid}’ from the post table?

absolutely, yes :slight_smile:

SELECT users.uid
     , users.name
     , users.email
     , user_groups.name AS group_name
     , user_groups.premium
     , posts.pid
     , posts.name  AS post_name
     , posts.content
  FROM users
INNER
  JOIN user_groups
    ON user_groups.id = users.user_group_id           
LEFT OUTER
  JOIN posts
    ON posts.uid = users.uid
 WHERE users.uid = {$uid} -- note no quotes

The problem is, what happens when there are no posts to display? There will be no profile content with the current query.

that’s what the LEFT OUTER JOIN accomplishes – it returns all the posts for a user, but if there aren’t any, it still returns the user and his group

Okay thanks for your help once again, I’ve figured out what I did wrong.