SQL - Condition that matches two strings
I am trying to fetch data from a SQL developer that matches two row conditions. Both rows have one unique value (ID) and the 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 Result:
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
The output should display all lines based on ID
source to share
If I understand the question correctly, you want to extract all rows for some ID, where two different rows fulfill two different conditions. you can use a couple of operators in
:
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 using the operator exists
:
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 one request with or
and count
how many of them are met:
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)
source to share
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;
Basically "find identifiers that have two different strings that match the criteria." We then return all rows with that id
Edit
Your real request would look like this:
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 parentheses around AND are clearly redundant due to operator precedence, but can help with readability?
Also, note that checking a date range means that it includes the start date but excludes the end date, namely x >= start and x < end
source to share