Left Join pass variable inside parentheses query???

[GregL83]

Hey all,

 

I'm trying to write a query that invovles 3 tables.  The tables are: users, user_pictures, and pictures.  Basically, each user can have multiple user_pictures, linked by user_id, which relate to a picture in the picture table with a unique picture_id.

 

users cols: id, name

user_pictures cols: user_id, picture_id

pictures cols: id, url

 

I need to return user_id and picture url.

 

here is the query I have thus far...  I was trying to use a variable inside the parenthese query to say only look for pictures that are relevant to the current user from the outer select statement...  Is there a way to pass that variable to the inner select statement??? or just a better way to write this query???

 

$query = "SELECT u.id AS uid, u.username, u.sex, u.birth_date, p.url AS picture, u.register_date FROM users u LEFT JOIN (SELECT us.user_id, us.picture_id, up.url FROM user_pictures us LEFT JOIN pictures up ON us.picture_id = up.id WHERE us.user_id = uid AND up.status = 'active' ORDER BY us.picture_id ASC LIMIT 1) p ON u.id = p.user_id ORDER BY u.id DESC LIMIT 5";

[JustLikeIcarus]

This should work.

 

select users.id, pictures.url
from users, pictures, user_pictures
where user_pictures.user_id = users.id
and pictures.id = user_pictures.picture_id

[GregL83]

that is a much simpler query than mine and doesn't take into account for the one to many relationship for pictures to users...

[JustLikeIcarus]

Oh for multiple picture just change the where statement a little. Try this.

 

select users.id, users.username, users.sex, users.birth_date, pictures.url AS picture, users.register_date
from users, pictures, user_pictures
where users.id = user_pictures.user_id
and user_pictures.picture_id = pictures.id

[GregL83]

this doesn't work either... i'm trying to return only one row per user. so as in my query, i want to take the latest active picture for each user and only return a single user row for each user...

[Mchl]

Why LIMIT 1 in subquery?

[JustLikeIcarus]

Ok so you want to reference a parent query field.. maybe something like this.

select users.id as uid, users.username, users.sex, users.birth_date, 
(select pictures.url 
from pictures, users_pictures 
where pictures.id = user_pictures.picture_id 
and user_pictures.user_id = uid 
and pictures.status = 'active' 
order by pictures.id desc limit 1
) as picture, users.register_date
from users

[Mchl]

A correlated subquery... big no-no due to performance.

[JustLikeIcarus]

Yes that would be a perf issue. Well another option to get the response back that he wants would be something like.

 

select users.id, users.username, users.sex, users.birth_date, pictures.url AS picture, users.register_date
from users, pictures,
(select user_pictures.user_id as uid, max(user_pictures.picture_id) pid
from user_pictures, pictures 
where pictures.id = user_pictures.picture_id
pictures.status = 'active'
group by user_pictures.user_id) t1
where t1.uid = users.id
and pictures.id = t1.pid

[Mchl]

SELECT 
  u.id AS uid, u.username, u.sex, u.birth_date, p.url AS picture, u.register_date 
FROM 
  users u 
LEFT JOIN (
  SELECT 
    us.user_id, us.picture_id, up.url 
  FROM 
    user_pictures us 
  LEFT JOIN 
    pictures up 
  ON 
    us.picture_id = up.id 
  WHERE 
    up.status = 'active' 
) p 
ON u.id = p.user_id 
ORDER BY u.id DESC LIMIT 5

[GregL83]

Mchl, you are close.  The problem now, is that i'm returning the same user twice if the last pictures were by that user...

[GregL83]

i added the group by method from JLI's query and now have the expected results

 

thanks for the help guys