从另一个查询创建查询

Question

Looking for a little assistance.. I am trying to create a query to use as a parameters for another query.

Here is what I am attempting..

I think I didnt make myself clear enough (big surprise) ignore any hard coded data in the queries, they first part is using variables sent in from the search page.

The second query is what I really need to know how to accomplish. If I have to figure out how to generate the sql for selecting however many records i got in the first query,,,

SELECT distinct Invoice_Number   FROM invoices_details where check_number = '9999' and taxid = '9999999'

Which returns 3 invoice numbers..

What I need to do is to use those invoice numbers in another select...

***Select claim_details from claim_d where invoice in ('xxxx','yyyy','zzzz')***

I am at a loss on if this is even possible..

Any help would be appreciated.

Thanks

Kevin

Answer 1

You can wrap that SELECT in your IN clause which is also known as subquery like

Select claim_details 
from claim_d 
where invoice in (
SELECT Invoice_Number   
 FROM invoices_details 
 where check_number = '9999' 
 and taxid = '9999999'
)

(OR) using a INNER JOIN like below (recommended approach)

select distinct claim_details 
from claim_d c
join invoices_details i ON c.invoice = i.Invoice_Number
where i.check_number = '9999' 
and i.taxid = '9999999';