Member Avatar for lifeworks

Hi

Not the first time Ive wondered about this, just the first time Im asking the internet :)

I have a list of 'users', and there is a table of 'photos'... each matched to a user via a 'link' table...

When I use a left join, and the 'user' doesnt have a photo... the left join excludes all users who dont have photos... which is not good.

How do I write a query which pulls out a user, and an array of all of the pictures attached to them?

That would be AWESOME.

  • 2 Contributors
  • 1 Reply
  • 80 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

The left join is exactly for pulling out ALL users, including those who don't have a photo.
Is it correct that you can have several users for the same photo? Otherwise you would not need a join table.
With the join table the query runs like:

select u.id, p.id from users u 
left join users_and_photos u_p
on u.id=u_p.id_user
join photos p
on u_p.id_photo=p.id

You can also have all the photos in one row using the group_concat function.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.