CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloper
Mikail
Mikail

Reputation: 63

fetching multiple data with sql query

I have a header and line detail table. Such as;

Header table: TRANSACTIONS Line detail table: TRANSACTIONS_LINE_DETAIL

In TRANSACTIONS table: SQ_TRANSACTION_ID, CH_TRANSACTION_NAME,.. columns are included.

In TRANSACTIONS_LINE_DETAIL table: SQ_TRANSACTION_LINE_DETAIL_ID, RF_TRANSACTION_ID, CH_LINE_CODE,.. columns are included.

TRANSACTIONS_LINE_DETAIL table keeps one or more detail line for each transactions which kept on TRANSACTIONS table.

So my problem is;

I wanna write a query that fetches me transactions which has X,Y and Z line codes together. (CH_LINE_CODE).

I wrote like this;

SELECT DISTINCT 
   TR.RF_TRANSACTION_ID
FROM
   TRANSACTIONS_LINE_DETAIL TR
WHERE
   TR.CH_LINE_CODE IN ('X','Y','Z')

But this code could return me transcations that hasn't got ''Y' or 'X' or 'Z'. I mean i want all line codes included in my transaciton.

I want a query that fetches me transactions that could has

X, Y, Z

or

A, B, C, X, Y, Z

or

X, Y, Z, P

but NOT

X

or

X, Y

or

Z, Y, A, B

.

Upvotes: 0

Views: 139

Answers (2)

Sebastian Cichosz
Sebastian Cichosz

Reputation: 909

You may find this one useful. Here is SQL Fiddle.

SELECT rf_transaction_id
  FROM transactions_line_detail
 WHERE ch_line_code IN ('X', 'Y', 'Z')
 GROUP BY rf_transaction_id
HAVING COUNT(1) = 3

Upvotes: 2

asantaballa
asantaballa

Reputation: 4048

Try this

Select * From TRANSACTIONS_LINE_DETAIL td
Where 
  Exists 
    ( Select 1 From TRANSACTIONS_LINE_DETAIL td2
      Where td2.RF_TRANSACTION_ID = td.RF_TRANSACTION_ID
        And td2.CH_LINE_CODE = 'X'
    )
  And 
  Exists 
    ( Select 1 From TRANSACTIONS_LINE_DETAIL td2
      Where td2.RF_TRANSACTION_ID = td.RF_TRANSACTION_ID
        And td2.CH_LINE_CODE = 'Y'
    )
  And 
  Exists 
    ( Select 1 From TRANSACTIONS_LINE_DETAIL td2
      Where td2.RF_TRANSACTION_ID = td.RF_TRANSACTION_ID
        And td2.CH_LINE_CODE = 'Z'
    )

Upvotes: 1

Related Questions