How to show only one row

I have this table structure and the sample data as well. I want to get only one row of the data. But instead it is giving me rows equal to it's child records.

--DROP TABLE [Detail];
--DROP TABLE [Master];

--CREATE TABLE [Master]
--(
--ID INT NOT NULL PRIMARY KEY,
--Code VARCHAR(25)
--);

--INSERT INTO [Master] VALUES (1, 'CASH');
--INSERT INTO [Master] VALUES (2, 'CASH');

--CREATE TABLE [Detail]
--(
--ID INT NOT NULL PRIMARY KEY,
--MasterID INT,
--DrAmount Numeric,
--CrAmount Numeric,
--CONSTRAINT FK_MASTER FOREIGN KEY (MasterID)
--REFERENCES [Master](ID)
--);

--INSERT INTO [Detail] VALUES (1, 1, '2200', NULL);
--INSERT INTO [Detail] VALUES (2, 1, NULL, '3200');
--INSERT INTO [Detail] VALUES (3, 1, '1000', NULL);
--INSERT INTO [Detail] VALUES (4, 2, NULL, '3200');
--INSERT INTO [Detail] VALUES (5, 2, '3200', NULL);


Here is the query and result:

SELECT [MASTER].[Code], [DETAIL].[MasterID], [DETAIL].[CrAmount]
FROM [MASTER], [DETAIL]
WHERE [MASTER].[ID] = [DETAIL].[MasterID]


SELECT TOP 1 ...

join

1 Answer
1

Looks like you need GROUP BY and as @HoneyBadger suggests, it would be better to use the modern explicit join syntax - it is much more clear:

select m.code, d.masterid, sum(d.cramount) amount
from [master] m
join[detail] d on m.[id] = d.[masterid]
group by m.code, d.masterid


Result:

code masterid amount
CASH 1 3200
CASH 2 3200


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.