fetching multiple data with sql query

Question

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

.

Sebastian Cichosz · Accepted Answer · 2013-08-28 12:58:31Z

2

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

answered Aug 28, 2013 at 12:58

Sebastian Cichosz

90913 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mikail Over a year ago

Thanks! that costs much less.

asantaballa · Accepted Answer · 2013-08-28 11:21:20Z

1

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

answered Aug 28, 2013 at 11:21

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Collectives™ on Stack Overflow

fetching multiple data with sql query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related