You need to join the execution and resource table to be able to get the results
you want. The where clause specifies which rows of the cross-product you want
returned.
select r.name, e.name, e.release
from execution e, resource r
where r.status like 'Busy (%)'
and substr(r.status,7,5) = e.run_id;
or, if you want to use some syntactic sugar to separate the join condition from
the filter.
select r.name, e.name, e.release
from execution e
join resource r
on substr(r.status,7,5) = e.run_id
where r.status like 'Busy (%)'
Overloading the status field in the resource table to contain two data items is
bad design. You should have a separate status and run_id columns in this table.
> I'm doing a select like this:
> select e.name, e.release from execution as e where run_id
> in (select substr(status,7,5) from resource where status like "Busy (%");
>
> I want to get to get a column from each row of the subselect (from
> resource) to match each result row.
>
>
> resource table:
> name status
> serverA Busy (28610)
> serverB Busy (28648)
>
> execution table:
> run_id release name
> 28610 rel1 run_name1
> 28648 rel2 run_name2
>
> The query returns:
> run_name1 rel1
> run_name2 rel2
>
> I want:
> serverA run_name1 rel1
> serverB run_name2 rel2
>
>
> I can do a query for each row returned by my subselect but I'm hoping to
> do it in one SQL statement.
