[RESOLVED] problem with a join

[chrishawaii]

dear sql-sharks,

i have to realize an request to an mysql-database.
for me its impossible to solve.
er-model (1:n):
table PHASE:
id
volume_id <--- VOLUME

result
revision

the first request is like the following one:

select p1.volume_id from phase p1
where(lower(p1.name) like '%k00t01%')

that gives me the id's of phase with a given name-string.
the second request shall give me the "subset":

select v1.id from volume v1
where(lower(v1.name) like '%test%')

my question is how to join these requests in a subsetrequest?
i tried this:

select p1.volume_id from phase p1
where(lower(p1.name) like '%k00t01%') in
(select v1.id from volume v1
where(lower(v1.name) like '%test%'))

but this is not allowed. i hope you understand my question?

any hint would be great and helpful.

best regards
chris

[chrishawaii]

did it with an inner join like:

SELECT * FROM vorgang v1 INNER JOIN lebensphase l1 ON (l1.id = v1.lebensphase_id) WHERE (LOWER(v1.bez) LIKE '%test%' AND LOWER(l1.bez) LIKE '%other%')