CODEWITHSUNDEEP
sqldatabasevariables
Jefferson Hudson
Jefferson Hudson

Reputation: 738

How can I use the result of one select statement as a variable in another?

This is what I'm trying to do

WITH 
loc_id AS ( // take the result of the following select statement
SELECT l.location_id
  FROM mopdb.lulocation l
  WHERE (l.location_short = 'FOO'))

SELECT d.device
  FROM mopdb.ludevice d
  LEFT JOIN lulocation l ON (d.location_id = l.location_id)
  WHERE (d.location_id = loc_id) //use it here

Upvotes: 0

Views: 65

Answers (2)

Mike Monteiro
Mike Monteiro

Reputation: 1457

Ugly, but: 

   SELECT d.device
      FROM mopdb.ludevice d
      LEFT JOIN lulocation l ON d.location_id = l.location_id
      WHERE d.location_id = 
       (
        SELECT x.location_id
        FROM mopdb.lulocation x
        WHERE (x.location_short = 'FOO')
      )

Upvotes: 1

Steve
Steve

Reputation: 1243

If you're trying to use a Common Table Expression then loc_id will be a table that can be used like a subquery, so you could do something like:

with loc_id as (
    select l.location_id
    from mopdb.lulocation l
    where (l.location_short = 'FOO')
) select
    d.device
from mopdb.ludevice d
left join lulocation l on d.location_id = l.location_id
where d.location_id = (select location_id from loc_id);

Variables however will have a different syntax in every dialect of SQL so it will depend heavily on what product your using.

Upvotes: 1

Related Questions