I hope you have created the 2 tables with parent and child relation ship like primary key should required on 1st table on DrugId column and Foreign key on 2nd table drugId column by referring 1st table drugid column.
Now use INNER JOIN concept and make grouping on 2 tables and get the desired results
Select DrugDr.drname as drname, UseDr.drugid as usedcount from UseDr INNER JOIN DrugDr ON UseDr.drugid = DrugDr.drugid
Hope the above is useful for your requirement.Thanks! B.Ramana Reddy
thanks for reply ramanna, but there is a small change in this tables. table 1 and table 2 joins with drugid and drugusedid but table 2 drugname will be same for more than one drugusedid (this is used for patient). for example drug aa used for d1 and d2. Please refer below table Ttable UseDr id drugid usedcount 1 d1 3 2 d2 3 3 d3 5 4 d2 1 5 d4 8
TableDrugDr drugusedid drname d1 aa d2 aa d3 bb d4 bb d5 cc