Subquery question...

[gjsaturday]

I have a query that gives me a list of dates (3 columns, id, from date, and to date). I want to incorporate this query result using between in the where clause. Is this possible?

i.e.

Query results
id from to
1 1/1/05 1/3/05
2 1/2/05 1/5/05

etc...

I want to select the corresponding data in another table
for id 1 for dates between 1/1/05 and 1/3/05 and
for id 2 for dates between 1/2/05 and 1/5/05, etc...

 

[checkai]

select id,
       from,
       to,
       (select field from table where date between to and from) as field
from table

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[gjsaturday]

this is the query I'd like to use as a subquery...

select store.name, a.date,min(b.date)-1
from
location parts
inner join inventory a on parts.id = a.id and a.shipin > 0 and a.shipout > 0
inner join inventory b on parts.id = b.id and b.shipin > 0 and b.shipout > 0 and b.date > a.date
inner join location item on parts.parent = item.id
inner join location store on item.parent = store.id
inner join locationextrainfo x on item.id = x.id
where parts.id in (893,894,895,896,897,898,899,900,901,902,903,904)
and a.date >= '03/01/2005'
group by d.name, a.date
order by a.date, min(b.date)-1
[code]

need more detail from inventory based on the from, to dates of the code above...

TIA!