CODEWITHSUNDEEP
sqlsql-servert-sql
Grzegorz G.
Grzegorz G.

Reputation: 1345

Join table and pick rows where for given id exists only one value

I don't know, if I made good title, but please let me visualize this.

enter image description here

So I have two tables and for given case I need to select row where payment currency was ONLY in EUR.

Correct document Id's will be: 2, 3, 4, 5

These are overall bigger tables with 900k+ records.

Can you please suggest me how query should look?

Upvotes: 3

Views: 725

Answers (5)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Aggregation is the only efficient want : 

select doc_id
from table t
group by doc_id
having min(currency) = max(currency) and min(currency) = 'EUR';

Upvotes: 1

StepUp
StepUp

Reputation: 38134

It is possible to use INNER JOIN with the following conditions to get all rows:

SELECT
  pd.payment_doc_id
, pd.currency
FROM DocTable dt 
INNER JOIN PaymentDocs pd 
    ON dt.document_id = pd.payment_doc_id AND pd.currency IN ('EUR')

If you want distinct rows, then you can apply operator GROUP BY:

SELECT
  pd.payment_doc_id
, pd.currency
FROM DocTable dt 
INNER JOIN PaymentDocs pd 
    ON dt.document_id = pd.payment_doc_id AND pd.currency IN ('EUR')
GROUP BY   pd.payment_doc_id
         , pd.currency

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use correlated subquery with not exists

select distinct a.document_id from tablename a inner join tablename b b on a.document_id=b.payment_docid
where not exists 
   (select 1 from tablename b1 where b1.payment_docid=b.payment_docid and currency<>'EUR')

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use corelated subquery

select t1.* from table2 as t1
 where exists( select 1 from table2 t2 where t1.payment_docid=t2.payment_docid
                       having count(distinct currency)=1)
and currency='EUR'

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37377

Try this query:

select payment_docId from MyTable
group by payment_docId
having max(currency) = 'EUR'
   and min(currency) = 'EUR'

or you could use having count(*) = 1 with min or max as well.

Upvotes: 3

Related Questions