CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Get child accounts

I have this table structure and some sample data as well. The logic here is that against any COATitle I enter child records that are the expenditures against that specific head account. In my sample data as Medical have multiple child records and same as Incentive.

My problem is that when I select specific head account like if I select Medical it should show its child records in this case they should not include records with MasterID 2 as they are of Incentive

CREATE TABLE TransactionMaster
(
ID Int,
TransactionCode VARCHAR(25),
PRIMARY KEY (ID)
)

INSERT INTO TransactionMaster VALUES (1, 'TRA-001');
INSERT INTO TransactionMaster VALUES (2, 'TRA-002');
INSERT INTO TransactionMaster VALUES (3, 'TRA-003');

CREATE TABLE TransactionDetail
(
ID Int,
MasterID Int,
COATitle VARCHAR(25),
CrAmount NUMERIC(18,2),
DrAmount NUMERIC(18,2),
PRIMARY KEY (ID),
FOREIGN KEY (MasterID) REFERENCES TransactionMaster(ID)
)


INSERT INTO TransactionDetail VALUES (1, 1, 'Medical', '2500', NULL)
INSERT INTO TransactionDetail VALUES (2, 1, 'Travelling', NULL, '2500')
INSERT INTO TransactionDetail VALUES (3, 1, 'Medicine', NULL, '2500')
INSERT INTO TransactionDetail VALUES (4, 1, 'Doc Fee', NULL, '2500')
INSERT INTO TransactionDetail VALUES (5, 2, 'Incentive', '3000', NULL)
INSERT INTO TransactionDetail VALUES (6, 2, 'Extra', NULL, '2500')
INSERT INTO TransactionDetail VALUES (7, 2, 'Bonus', NULL, '500')
INSERT INTO TransactionDetail VALUES (8, 3, 'Medical', NULL, '3000')
INSERT INTO TransactionDetail VALUES (9, 3, 'Tests', '2500', NULL)
INSERT INTO TransactionDetail VALUES (10, 3, 'Juice', '500', NULL)

Query Sample:

SELECT [Voucher].[TransactionCode], [Detail].[COATitle], [Detail].[CrAmount], [Detail].[DrAmount] 
FROM [TransactionMaster] [Voucher], [TransactionDetail] [Detail]
WHERE [Voucher].[ID] = [Detail].[MasterID] AND COATitle NOT IN ('Medical')

OutPut:

TransactionCode           COATitle                  CrAmount                                DrAmount
------------------------- ------------------------- --------------------------------------- ---------------------------------------
TRA-001                   Travelling                NULL                                    2500.00
TRA-001                   Medicine                  NULL                                    2500.00
TRA-001                   Doc Fee                   NULL                                    2500.00
TRA-002                   Incentive                 3000.00                                 NULL
TRA-002                   Extra                     NULL                                    2500.00
TRA-002                   Bonus                     NULL                                    500.00
TRA-003                   Tests                     2500.00                                 NULL
TRA-003                   Juice                     500.00                                  NULL

The desired output shouldn't include rows with TransactionCode with 'TRA-002'.

Upvotes: 2

Views: 29

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following query

SELECT
  m.TransactionCode,
  d.COATitle,
  d.CrAmount,
  d.DrAmount
FROM TransactionDetail d
JOIN TransactionMaster m ON d.MasterID=m.ID
WHERE d.MasterID IN(
      SELECT MasterID
      FROM TransactionDetail
      WHERE COATitle='Medical'
    )
  AND d.COATitle<>'Medical'

Upvotes: 2

Related Questions