• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 GOracle 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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