Display records from Master/Master-Detail Table using SQL query only

Question

I have a problem which I already solved using T-SQL(cursor & loop).(SQL server 2005)

But I am looking for the solution using SQL.

I have a Master Table with a column say MasterRecord(all are unique and type Varchar and PK)

MasterRecord
------------
MRecord1
MRecord2
MRecord3
MRecord4
................
..................
MRecord[n]

Now the Master-Detail table has two columns MasterRecord(Varchar & FK) and DetailRecord(Varchar)

MasterRecord          DetailRecord
---------------------------------------------
MRecord1              MRecord1_DetailRecord1
MRecord1              MRecord1_DetailRecord2
MRecord1              MRecord1_DetailRecord3
MRecord1              MRecord1_DetailRecord4
MRecord2              MRecord2_DetailRecord1
MRecord2              MRecord2_DetailRecord2
MRecord2              MRecord2_DetailRecord3
MRecord2              MRecord2_DetailRecord4
...............................................
................................................
MRecord[n]            MRecord[n] _DetailRecord1
MRecord[n]            MRecord[n] _DetailRecord2
MRecord[n]            MRecord[n] _DetailRecord3
MRecord[n]            MRecord[n] _DetailRecord4

where [n] can be any number

The problem is that for each unique Master Record, I should fetch the top 2 detail records

O/P:

MasterRecord          DetailRecord
---------------------------------------------
MRecord1              MRecord1_DetailRecord1
MRecord1              MRecord1_DetailRecord2
MRecord2              MRecord2_DetailRecord1
MRecord2              MRecord2_DetailRecord2
MRecord3              MRecord3_DetailRecord1
MRecord3              MRecord3_DetailRecord2
...............................................
..............................................
MRecord[n]            MRecord[n] _DetailRecord1
MRecord[n]            MRecord[n] _DetailRecord2

Hope I clearly explained my problem.

Please let me know for further clarification.

Answer 1

Try this:

WITH cteCount as 
(
    Select 
         ROW_NUMBER() OVER(PARTITION BY MRecord ORDER BY MR_DETAIL_COLUMN) as TopCnt,
         MR_DETAIL_COLUMN
    FROM MASTER_DETAIL_TABLE
)
SELECT 
       *
FROM   MASTER_TABLE as MT
JOIN   cteCount as MDT ON MDT.MRecord = MT.MRecord
WHERE  TopCnt <= 2

Edit: corrected spelling typo

Edit: corrected really dumb mistakes

Answer 2

Not sure if you wanted just records with two or records with one and two.

Have a look here and let me know.

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

Hope that helps

Answer 3

I don't have time to write out the full query now, but what you do is start with the master table and join in the detail table twice. The first join should match the top record (where the count of those lesser = 0, for your particular definition of 'lesser') for each master record, and the 2nd join should match the 2nd record (where the count of those lesser = 1).

Update
As I think about it, you'll have to do a union to get your additional record (still write the same join, but in a completely separate select query that you include in the results via union). Otherwise, you have to return your output with both the first and second detail keys in the same record.