I have a query I want to run through Drupal 7's DB API as a dynamic query where a subquery needs to be a column of data.

For example:

SELECT a, b, (
  SELECT x, y
    FROM table
    WHERE table.x = 'value') as c
FROM table_2
WHERE table_2.a = 'value_2'

How can I perform this through the Drupal 7's DB API?


If you're building a dynamic query, the method SelectQuery::addExpression() will let you add a SQL subquery expression to the list of selected columns:

$query = db_select('table_2', 't2')
  ->fields('t2', array('a', 'b'))
  ->condition('t2.a', 'value_2');

$sub_query = "(SELECT x, y
  FROM table
  WHERE table.x = 'value')";

$query->addExpression($sub_query, 'c');
$results = $query->execute();
| improve this answer | |

You can use db_query()

$result = db_query(
  'SELECT a, b, (
    SELECT x, y
    FROM {table}
    WHERE table.x = :value1) as c
  FROM {table_2} AS table_2
  WHERE table_2.a = :value2',
    ':value1' => 'value',
    ':value2' => 'value_2',
| improve this answer | |
  • I should have mentioned this was in the context of a dynamic query, but yeah this method works too. Thanks! – Shawn Conn Mar 8 '17 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.