如何使用like进行单个查询

How I make the below queries in a single query.

我如何在单个查询中进行以下查询。

$result = db_query("SELECT replace(source, 'node/', '') as source FROM {url_alias}  where source like 'node/%' limit 11");
foreach ($result as $record) {
$result2 = db_query("SELECT fci.* FROM {node} AS fci LEFT JOIN {node_access} AS fdfp ON fci.nid = fdfp.nid where fci.nid = :item_id AND fdfp.nid IS NULL", array(':item_id' => $record->source));
  foreach ($result2 as $record2) {
    echo '<pre>';
    print_r($record2);
  }
}

2 个解决方案

#1

SELECT fci.*
  FROM {node} AS fci
  INNER JOIN {url_alias} ON fci.nid = REPLACE(source, 'node/', '')
  LEFT JOIN {node_access} AS fdfp ON fci.nid = fdfp.nid
  WHERE source LIKE 'node/%' AND fdfp.nid IS NULL
  LIMIT 11

#2

$result2 = db_query("SELECT fci.* FROM {node} AS fci LEFT JOIN {node_access} AS fdfp ON fci.nid = fdfp.nid where fci.nid IN(SELECT replace(source, 'node/', '') as source FROM {url_alias}  where source like 'node/%') AND fdfp.nid IS NULL");
foreach ($result2 as $record2) {
  echo '<pre>';
  print_r($record2);
}