How to write the query

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

select * from table where Status ='Confirmed"

Sorry

Select * FROM table WHERE status ="Confirmed"

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

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'

what if i dont want to hard code the tax_number

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(*)

Select allOpen in new window

we are guessing at what you need exactly ...
if this is for learning sql, explain in more detail your problem

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?

yes that is my requirement

@pardeshirahul - Did you run the query I provided?