If you have a usertable and an imagetable, naturally you would join them to display a thumbnail for the user. If you are pulling 20 users per page and want the join to pull a photo marked as default for each user and a regular photo if none is marked, is that possible?
The project I am working on does not force them to select a default image so some users have one and some do not. If they all had a default, I could make the join read && default = 1. If I were to try to order by default, it would order the entire result by 1 or 0 (usernames with 1, usernames without).
So what I really need is some way of making it have a preference.
select a.name, a.age, a.category, a.info1, a.info12, a.lastlogin, a.lastactivity, b.url from usertable a INNER JOIN imagetable b ON a.name = b.name and b.status = 'active' && b.priv != 1
If I add && b.default = 1 then I eliminate all users without a default, which I do not wish to do. So I need something like “&& b.default = 1,0”
Is there a way to write the join to order only the image table and select one with a 1 if it exists?
for those who don’t have a default image, presumably they might have several images with default=0, therefore you will ahve to have some mechanism to choose only one
the following example uses latest uploaded date for the images
SELECT a.name
, a.age
, a.category
, a.info1
, a.info12
, a.lastlogin
, a.lastactivity
, b.url
FROM usertable AS a
INNER
JOIN imagetable AS b
ON b.name = a.name
and b.status = 'active'
and b.priv <> 1
and b.default = 1
UNION ALL
SELECT a.name
, a.age
, a.category
, a.info1
, a.info12
, a.lastlogin
, a.lastactivity
, b.url
FROM usertable a
INNER
JOIN ( SELECT name
, MAX(date_uploaded) AS last_uploaded
FROM imagetable
WHERE status = 'active'
AND priv <> 1
GROUP
BY name ) AS m
ON m.name = a.name
INNER
JOIN imagetable AS b
ON b.name = a.name
and b.date_uploaded = m.last_uploaded
WHERE NOT EXISTS
( SELECT 1
FROM imagetable
WHERE name = a.name
AND priv <> 1
AND default = 1 )
Something like this would give you a semi-random image from all their images when none have been marked as a default. Otherwise when an image has been marked as default you would get that image.
SELECT
u.users_id
,COALESCE(u2i.images_id,u2i2.images_id) images_id
FROM
users u
LEFT OUTER
JOIN
users_to_images u2i
ON
u.users_id = u2i.users_id
AND
u2i.default_image = 1
LEFT OUTER
JOIN
users_to_images u2i2
ON
u.users_id = u2i2.users_id
GROUP
BY
u.users_id