[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

How to write the query

I have a table

it has 3 coulumns

Tax_id, tax_number, Tax_status

now
suppose the data is

tax_id 1
tax_number 123
Tax_status Open

Tax_id 1tax_number 1234
tax_status - closed

tax_id 1
Tax_number 12345
tax_status - Confirmed

now i want the query

for whicg

find a  Tax_id
under which all
the tax_number ( 123, 12354,12345 )
has status'COnfirmed'
0
pardeshirahul
Asked:
pardeshirahul
  • 3
  • 2
  • 2
  • +3
1 Solution
 
johnsoneSenior Oracle DBACommented:
Not sure this is what you are looking for, but based on the description, it should do what you need:

select tax_id from tbl
where tax_number = 123 and status = 'Confirmed' and
exists (select 1 from tbl where tax_number = 12354 and status = 'Confirmed') and
exists (select 1 from tbl where tax_number = 12345 and status = 'Confirmed');
0
 
plusone3055Commented:
select * from table where Status ='Confirmed"
0
 
plusone3055Commented:
Sorry

Select * FROM table WHERE status ="Confirmed"
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
johnsoneSenior Oracle DBACommented:
Sorry, I missed part of the exists in my original post and cannot edit it now.  Here is the correct version:

select tax_id from tbl a
where tax_number = 123 and status = 'Confirmed' and
exists (select 1 from tbl b where tax_number = 12354 and status = 'Confirmed' and a.tax_id = b.tax_id) and
exists (select 1 from tbl c where tax_number = 12345 and status = 'Confirmed' and a.tax_id = c.tax_id);
0
 
pardeshirahulAuthor Commented:
nope

i want just the Tax_id under which if there are 10 Tax_number
all of which with status='CONFIRMED'

not just one tax_id under which any tax_number with tax_status='Confirmed'
0
 
pardeshirahulAuthor Commented:
what if i dont want to hard code the tax_number
0
 
SharathData EngineerCommented:
check this
select Tax_id
from table_name
group by Tax_id   
having SUM(case when Tax_status = 'Confirmed' then 1 else 0 end) = COUNT(*)

Open in new window

0
 
Geert GruwezOracle dbaCommented:
we are guessing at what you need exactly ...
if this is for learning sql, explain in more detail your problem
0
 
awking00Commented:
So in your example, tax_id 1 would not be returned because all of the statuses are not 'Confirmed'? But, if there were a tax_id of 2 that had tax_numbers of 456 and 4567 both with statuses of 'Confirmed', it would be returned?
0
 
pardeshirahulAuthor Commented:
yes that is my requirement
0
 
awking00Commented:
select tax_id from table where tax_status = 'Confirmed'
minus
select tax_id from table where tax_status != 'Confirmed';
0
 
SharathData EngineerCommented:
@pardeshirahul - Did you run the query I provided?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now