mysql - 两个 SQL 查询的组合

Question

我有两个单独的 SQL 查询，如果可能的话，我想将它们合并为一个。

查询 #1 以随机顺序生成表中的所有条目

查询#2 之后会检查结果是否可用

我怎样才能直接在 SQL 中一步实现这一点？

代码:

// start with a query for all of the photos, returned in random order
$query = "
SELECT DISTINCT m.mediaID
              , m.description
              , m.path
              , m.alwayson
              , m.usecollfolder
              , m.mediatypeID 
FROM $media_table m
WHERE m.mediatypeID = 'photos' 
ORDER BY RAND();
"; 
$result = mysql_query($query) or die ("$text[cannotexecutequery]: $query");
while( $imgrow = mysql_fetch_assoc( $result ) ) {

    // if the picture is alwayson or we are allowing living to be displayed,
    // we don't need to bother 
    // with any further checking  
    if ($imgrow[alwayson] || $allow_living_db ) { 
    break; 

    // otherwise, let's check for living  
    } else { 

    // this query will return rows of personIDs on the photo that are living  

    $query = "    
    SELECT l.personID 
    FROM $medialinks_table l
    JOIN $people_table p ON l.personID = p.personID 
    WHERE l.mediaID = $imgrow[mediaID] 
    AND p.living = 1
     ";
    $presult = mysql_query($query) or die ("$text[cannotexecutequery]: $query");
    $rows = mysql_num_rows( $presult );
    mysql_free_result( $presult );

    // if no rows are returned, there are no living on the photo, so let's display it  
    if ($rows == 0) {
        break;
    }
}

Answer 1

SELECT DISTINCT m.mediaID
              , m.description
              , m.path
              , m.alwayson
              , m.usecollfolder
              , m.mediatypeID 

              , l.personID 

           FROM $media_table m

           JOIN $medialinks_table l
             ON l.mediaID = m.mediaID
           JOIN $people_table p
             ON l.personID = p.personID 
          WHERE m.mediatypeID = 'photos'
            AND p.living = 1

          ORDER 
             BY RAND();