SQL - Condition which Matches two rows

Question

I am trying to extract the data from SQL developer which matches two row conditions. Both the rows has one unique value ( ID ) and table name is abc.tcd

ID  =  Type =   GL code = amount
1   =   Debit =  0701  =  10000
1  =    credit = 0601 =   10000
1  =    Credit=  0501  =  1000
1   =   Debit=   0401   = 1000
2   =   Debit =  0701  =  9000
2   =   credit = 0801  =  9000
3   =   Debit  = 0701  =  6000
3   =   credit = 0601  =  6000

Condition 1 :

GL code = '0701'   having Type = 'Debit' 

condition 2 :

GL code = '0601'   having Type = 'Credit' 

Expected output :

ID    Type    GL code  amount

1  =    Debit  = 0701 =   10000
1  =    credit = 0601 =   10000
1  =    Credit = 0501 =   1000
1  =    Debit  = 0401 =   1000
3  =    Debit  = 0701 =   6000
3  =    credit = 0601 =   6000

Output should display all the rows based on ID

Answer 1

If I understand the question correctly, you want to extract all the rows for some ID where two different rows fulfill two different conditions. you could use a couple of in operators:

SELECT *
FROM   mytable
WHERE  id IN (SELECT id
              FROM   my_table
              WHERE  GLCode = '0701' AND Type = 'Debit')
        AND id IN (SELECT id
                   FROM   my_table
                   WHERE  GLCode = '0601' AND Type = 'Credit')

Of course, this can be easily translated to use the exists operator:

SELECT *
FROM   mytable a
WHERE  EXISTS (SELECT *
               FROM   my_table b
               WHERE  a.id = b.id AND b.GLCode = '0701' AND b.Type = 'Debit')
        AND EXISTS (SELECT *
                    FROM   my_table c
                    WHERE  a.id = c.id AND c.GLCode = '0601' AND c.Type = 'Credit')

A more elegant way might be to have all the conditions in a single query with ors and count how many of them are fulfilled:

SELECT *
FROM   mytable
WHERE  id IN (SELECT   id
              FROM     my_table
              WHERE    (GLCode = '0701' AND Type = 'Debit') OR
                       (GLCode = '0601' AND Type = 'Credit')
              GROUP BY id
              HAVING   COUNT(*) = 2)

Answer 2

Another alternative:

SELECT * FROM MyTable
INNER JOIN
(
  SELECT ID
  FROM MyTable
  WHERE (GLCode = '0701' AND Type = 'Debit') OR (GLCode = '0601' AND Type = 'Credit')
  GROUP BY ID
  HAVING COUNT(DISTINCT GLCode) = 2 AND COUNT(DISTINCT Type) = 2
) X
ON MyTable.ID = x.ID;

SqlFiddle here

Basically "find the ids having two distinct rows meeting the criteria". We then return all rows with this ID

Edit

Your real query would look like:

SELECT *
FROM tbaadm.ctd 
INNER JOIN
(SELECT Tran_id 
    FROM tbaadm.ctd 
    WHERE ((GL_SUB_HEAD_CODE = '06106' AND PART_TRAN_TYPE = 'C') 
          OR (GL_SUB_HEAD_CODE = '29101' AND PART_TRAN_TYPE = 'D'))
       AND (tran_date >= '01-12-2014' AND tran_date < '30-12-2014')
    GROUP BY Tran_id
    HAVING COUNT(DISTINCT GL_SUB_HEAD_CODE) = 2 AND COUNT(DISTINCT PART_TRAN_TYPE) = 2
) X
ON tbaadm.ctd = x.Tran_id;

The parenthesis around the ANDs are obviously redundant due to operator precedence, but might help with readability?

Also, note with Date range checking that it is convention to include start date but exclude end date, viz x >= start and x < end