Help creating a SQL Query

Question

Somewhat new to SQL queries and I need a little help with my join.

I am supplied with gid

For each of these I need to grab name from table wp_ngg_gallery

then join in table wp_ngg_pictures and grab field filename limit 1 order DESC by field imagedate

Anyone able to help?

Answer 1

Let me know how / if this works. If you dump both of those tables (out of phpMyAdmin) in a SQL format I will create local databases and give it a run myself (assuming this doesn't work).

SELECT
    `wp_ngg_gallery`.`name`,
    `wp_ngg_pictures`.`filename`
FROM
    `wp_ngg_gallery`
    LEFT JOIN
        `wp_ngg_pictures`
    ON
        `wp_ngg_gallery`.`gid` = `wp_ngg_pictures`.`galleryid`
ORDER BY
    `wp_ngg_pictures`.`imagedate` DESC
LIMIT
    0,1;

This is assuming you need a JOIN. It could probably be done with a decent GROUP BY statement.

Answer 2

I'm going to guess:

select wp_ngg_gallery.name, wp_ngg_pictures.filename
from wp_ngg_gallery, wp_ngg_pictures
where wp_ngg_pictures.galleryid = wp_ngg_gallery.gid
order by wp_ngg_pictures.imagedate DESC 
limit 0,1

If there's a specific GID you want, you could do:

select wp_ngg_gallery.name, wp_ngg_pictures.filename
from wp_ngg_gallery, wp_ngg_pictures
where wp_ngg_pictures.galleryid = wp_ngg_gallery.gid
and wp_ngg_gallery.gid = $gid
order by wp_ngg_pictures.imagedate DESC 
limit 0,1

(The $gid is valid if you're query is a string in PHP, you didn't say, though, if that's what you're using)

It's not clear if wp_ngg_pictures.galleryid is a foreign key to wp_ngg_gallery.gid but it's the most likely choice given the info you supplied. If it's not (if you can have galleries with no images), you might want to modify the other guy's query (using the outer join) by adding and wp_ngg_gallery.gid = $gid to it.

Answer 3

If you want it for multiple gids (choose the other options for a single one):

SELECT gid,name, a.filename
FROM wp_ngg_gallery
JOIN  wp_ngg_pictures a
ON a.galleryid = gid 
LEFT JOIN wp_ngg_pictures b
ON a.galleryid = gid 
AND b.imagedate > a.imagedate
WHERE b.galleryid IS NULL
AND gid IN (1,2,3)