title: How do i merge two or more rows based on their foreign key?
body: <p>I have three tables which are:</p>
  
  <pre><code>A(a1, a2, a3)
  
  //This tbl (Table) can have multiple instances of a1, but cause of its dependence on b1, 
  //we have a unique record each time
  B(b1, a1, b2)
  
  //tbl C has a relationship with tbl B through b1. b1 can also have multiple instances, but 
  //sometimes a number of unique records in this table can tie to just one record in the B tbl.
  C(c1, b1, c2, c3)
  </code></pre>
  
  <p>Example:</p>
  
  <pre><code>//Table B sample data
  b1     a1     b2
  1      25     paper
  2      29     pencil
  3      29     parker
  
  //Table C sample data
  c1     b1     c2     c3
  1      1      w      long
  2      2      b      long
  3      2      g      short
  4      3      v      fat
  </code></pre>
  
  <p>Explanation:
  For the record 2 in tbl B, records 2 and 3 in tbl C should form a single record for it.</p>
  
  <p>From every thing so far, tbl A can have multiple records in tbl B which are distinct to tbl A. Tbl B can also have multiple  records in tbl C, but those multiple records must be merged into a single record (Where ever there is a duplicate foreign key in tbl C, then a merging should occur).</p>
  
  <p>I hope i have done a good job at explaining my problem. I need a query to do this. Can anyone help please?</p>
  
  <p>Addition #1:
  In an effort to make it clearer i'll use the really situation im faced with.</p>
  
  <p>Every drug is grouped under a main classification, sub classification, and a generic name.</p>
  
  <pre><code>-Main Classification
   -Sub Classification
    -Generic Name
  </code></pre>
  
  <p>Each generic name can have more than one strength:
  Example:</p>
  
  <pre><code> -TRIMETHOPRIM
    Strengths: 100mg, 200mg
  </code></pre>
  
  <p>There are also times where you can have a generic name that is a combination of two drugs and these two drugs have their strengths, which counts as on strength for this generic name. You can also have multiple.
  Example:</p>
  
  <pre><code> -SULFAMETHOXAZOLE &amp; TRIMETHOPRIM
    Strengths: 40mg &amp; 8mg/mL, 400mg &amp; 80mg, 800mg &amp; 160mg
  </code></pre>
  
  <p>So, to enable me keep track of the individual strengths of the combined generic name, i needed another table.</p>
  
  <pre><code>GenericTbl(Id, Name, ...)
  GenericDetails(Id, GenericId, ...)
  Strenghts(Id, GenericDetailsId, Strength, Unit, DosageForm, ...)
  </code></pre>
  
  <p>I hope this helps.</p>
  
  <p>Addition #2
  I have changed the ids to integers instead of guid (uniqueidentifier)</p>
  
  <pre><code>Pharmacy_GenericDrug
  GenericDrugID     GenericDrugName               DrugSubClassificationID     ControlStatusID
  1                 TRIMETHOPRIM                  12                          2
  2                 SULFAME &amp; TRIMETHOPRIM        4                           1
  
  Pharmacy_GenericDrugDetails
  GenericDrugDetailsID       GenericDrugID
  1                          1
  2                          2
  
  
  
  Pharmacy_Strengths
  StrengthID         GenericDrugDetailsID       DosageStrength        Unit
  1                  1                          200                   mg
  2                  2                          80                    mg
  3                  2                          8                     mg/L
  
  
  For the first#1 GenericDrugID = 1: TRIMETHOPRIM | 12 | 200 | mg | 1
  
  For the second#2 GenericDrugID = 2: SULFAME &amp; TRIMETHOPRIM | 4 | 80 | mg | 8 | mg/L | 1
  
  1 &gt; GenericDrugName | DrugSubClassificationID | DosageSrength | Unit | ControlStatusID
  
  2 &gt; GenericDrugName | DrugSubClassificationID | DosageSrength | Unit | DosageSrength | Unit | ControlStatusID
  </code></pre>