StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Not sure if you wanted just records with two or records with one and two. </p> <p>Have a look here and let me know.</p> <pre><code>DECLARE @Master TABLE( MasterRecordID VARCHAR(20) ) INSERT INTO @Master (MasterRecordID) VALUES ('MASTER1') INSERT INTO @Master (MasterRecordID) VALUES ('MASTER2') INSERT INTO @Master (MasterRecordID) VALUES ('MASTER3') INSERT INTO @Master (MasterRecordID) VALUES ('MASTER4') DECLARE @MasterDetail TABLE( MasterRecordID VARCHAR(20), MasterDetailRecord VARCHAR(50) ) INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER4','MASTERDETAIL10') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER3','MASTERDETAIL09') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER3','MASTERDETAIL08') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER3','MASTERDETAIL07') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER2','MASTERDETAIL06') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER2','MASTERDETAIL05') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER2','MASTERDETAIL04') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER1','MASTERDETAIL03') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER1','MASTERDETAIL02') INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER1','MASTERDETAIL01') DECLARE @MaxRecords INT SELECT @MaxRecords = 2 SELECT md.MasterRecordID, md.MasterDetailRecord FROM @MasterDetail md INNER JOIN --this section ensures that we only return master records with at least MaxRecords as specified (2 in your case) --if you wish to display al master records, with 1, 2 or MaxRecords, romove this section or see below ( SELECT MasterRecordID FROM @MasterDetail GROUP BY MasterRecordID HAVING COUNT(MasterRecordID) >= @MaxRecords ) NumberOfRecords ON md.MasterRecordID = NumberOfRecords.MasterRecordID INNER JOIN @MasterDetail mdSmaller ON md.MasterRecordID = mdSmaller.MasterRecordID WHERE mdSmaller.MasterDetailRecord <= md.MasterDetailRecord GROUP BY md.MasterRecordID, md.MasterDetailRecord HAVING COUNT(mdSmaller.MasterDetailRecord) <= @MaxRecords ORDER BY md.MasterRecordID, md.MasterDetailRecord SELECT md.MasterRecordID, md.MasterDetailRecord FROM @MasterDetail md INNER JOIN --this will ensure that all master records will return with 1, 2 or MaxRecords @MasterDetail mdSmaller ON md.MasterRecordID = mdSmaller.MasterRecordID WHERE mdSmaller.MasterDetailRecord <= md.MasterDetailRecord GROUP BY md.MasterRecordID, md.MasterDetailRecord HAVING COUNT(mdSmaller.MasterDetailRecord) <= @MaxRecords ORDER BY md.MasterRecordID, md.MasterDetailRecord </code></pre> <p>Hope that helps</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload