CODEWITHSUNDEEP
sql
user1977936
user1977936

Reputation: 31

SQL join 2 queries in one from same table

I have a table like that

IVA

id - taxa - name   - date
1  - 0.18 - normal - 1/1/2014
2  - 0    - none   - 1/1/2014 
3  - 0.23 - max    - 1/1/2013
4  - 0.16 - normal - 1/1/2015
...

I have an IVA.id from a product and I want to know in current date what is the taxa.

So I have to query this table twice:

Select name 
from IVA 
where id = 1; (return me normal)

Select taxa 
from IVA 
where name = 'normal' 
  and date <= '12/12/2015';

Can I join these two queries into only one?

Thanks

Upvotes: 0

Views: 63

Answers (4)

David Faber
David Faber

Reputation: 12486

You might try the following:

SELECT i1.name, i1.taxa
  FROM iva i1, iva i2
 WHERE i1.date <= '12/12/2015'
   AND i1.name = i2.name
   AND i2.id = 1

You don't specify a DBMS in your question so I'm not entirely certain that the above date format will work.

Upvotes: 0

Codeek
Codeek

Reputation: 1624

Why not use subqueries as below :

Select taxa 
from IVA 
where name=(select top 1 name from IVA where id = 1)
and date<='12/12/2015';

I have assumed that you might get different names in case there are duplicate IDs, hence Top 1. In case you want to use all the names used IN clause as :

Select taxa 
from IVA 
where name IN (select name from IVA where id = 1)
and date<='12/12/2015';

PS : Top 1 may not work in all scenarios. It's engine specific.

Upvotes: 0

user2941651
user2941651

Reputation:

Please try:

Select taxa 
from IVA 
where name in (Select a.name from IVA a where a.id = 1) 
and date<='12/12/2015';

Upvotes: 3

Blake
Blake

Reputation: 308

Depends on the database engine you're using, but you may want to look up the UNION keyword for your database.

Upvotes: 0

Related Questions