Creating a Query from another query

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

yes it can be used inside the where in clause

Kevin
– Kevin

2016-08-09 22:38:23 +00:00
Commented Aug 9, 2016 at 22:38 — Kevin
– Kevin, Commented Aug 9, 2016 at 22:38

Rahul · Accepted Answer · 2016-08-09 22:48:18Z

4

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';

edited Aug 9, 2016 at 22:48

answered Aug 9, 2016 at 22:38

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

sgeddes Over a year ago

I'd recommend removing distinct from the subquery, but other than that, looks good...

sgeddes Over a year ago

Well, with a join, you might need distinct on the outer query depending on how many rows are in the invoice_details table... I actually generally recommend exists, but there are subtle differences in all 3 approaches. Either way, correct answer for this question!

Strawberry Over a year ago

Ok. I'd still get rid of the first query though. (And the inverted commas)

Rahul Over a year ago

@Strawberry, don't think that would be a good idea since that's what was the original question of OP. But hey yes, edited post again to mention that JOIN would be recommended way to go.

Strawberry Over a year ago

Well, I guess we can agree to disagree, and I accept that both queries are valid, and it's not like you need the votes ;-)

|

Collectives™ on Stack Overflow

Creating a Query from another query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related