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

Question

Hi, 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.

Thanks in advance

Answer 1

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.

Answer 2

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 MASTER_DETAIL_TABLE as MDT ON MDT.MRecord = MT.MRecord
WHERE TopCnt <= 2

Edit: corrected spelling typo

Answer 3

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