COATitle
我输入子记录,这些记录是针对特定主帐户的支出。在我的示例数据中
Medical
有多个子记录,与
Incentive
.
我的问题是,当我选择特定的主帐户时,就像我选择
医疗
MasterID
2因为他们是
激励
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)
查询示例:
SELECT [Voucher].[TransactionCode], [Detail].[COATitle], [Detail].[CrAmount], [Detail].[DrAmount]
FROM [TransactionMaster] [Voucher], [TransactionDetail] [Detail]
WHERE [Voucher].[ID] = [Detail].[MasterID] AND COATitle NOT IN ('Medical')
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
所需的输出不应包含
TransactionCode
具有
'TRA-002'
.