1
$xcgalAlbums = $xoopsDB->prefix("xcgal_albums");
$xcgalPictures = $xoopsDB->prefix("xcgal_pictures");
$select_columns = "COUNT(pid) AS cpid, MIN(pid) AS pid, $xcgalAlbums.description, filepath, filename, url_prefix, filesize, pwidth, pheight, $xcgalPictures.aid, ctime, $xcgalAlbums.title, $xcgalAlbums.thumb";
$result = $xoopsDB->query("SELECT $select_columns FROM $xcgalPictures INNER JOIN $xcgalAlbums ON $xcgalAlbums.aid=$xcgalPictures.aid WHERE approved = 'YES' GROUP BY $xcgalPictures.aid ORDER BY $xcgalAlbums.category DESC, $xcgalAlbums.pos LIMIT $count");
Thats a sql-query for the xcgal-block. But i need an other inner join (?) in it to order the results a little different.
i want it to be ordered by $xcgalCategories.pos
join criteria would be: $xcgalAlbums.category=$xcgalCategories.cid
So what should the SQL look like?