title: How to do a join query on a dataset that has multiple tables using VB.NET
body: <p>I'm reading two XML files into one dataset. I do this by reading my first xml file into a dataset, read my second xml file into another dataset and I then merge the two datasets into one dataset. I'm not sure if this is the best and most efficient way to do this.</p>
  
  <pre><code>xmlFilePath = "C:\RESUSCITATION_INFUSION_DRUGS.xml"
  dsInfusions_min.ReadXml(xmlFilePath)
  
  xmlFilePath = "C:\RESUSCITATION_WEIGHT_CAT.xml"
  dsWeight.ReadXml(xmlFilePath)
  
  dsMain.Merge(dsInfusions_min.Tables(0))
  dsMain.Merge(dsWeight.Tables(0))
  </code></pre>
  
  <p>So now my dsMain dataset has two tables. My question is, how can I perform a select statement that would join the two tables such as the select below, and have the result merged into another dataset?</p>
  
  <pre><code>SELECT
      D.DRUG_NAME,
      D.DRUG_UNITS,
      D.PATIENT_WEIGHT_CATEGORY
  FROM
      RESUSCITATION_INFUSION_DRUGS D, RESUSCITATION_WEIGHT_CAT C
  WHERE
      D.PATIENT_WEIGHT_CATEGORY = C.PATIENT_WEIGHT_CATEGORY AND
      15 &lt;  C.MAXIMUM_WEIGHT and
      15 &gt;= C.MINIMUM_WEIGHT
  ORDER BY
      D.DRUG_NAME
  </code></pre>
  
  <p>Here are my two xml files...</p>
  
  <pre><code>&lt;?xml version='1.0'  encoding='UTF-8' ?&gt;
  &lt;RESUSCITATION_WEIGHT_CATEGORIES&gt;
      &lt;RESUSCITATION_WEIGHT_CATEGORY&gt;
          &lt;PATIENT_WEIGHT_CATEGORY&gt;00&lt;/PATIENT_WEIGHT_CATEGORY&gt;
          &lt;MINIMUM_WEIGHT&gt;0&lt;/MINIMUM_WEIGHT&gt;
          &lt;MAXIMUM_WEIGHT&gt;9999&lt;/MAXIMUM_WEIGHT&gt;
          &lt;CATEGORY_DESCRIPTION&gt;All patients, regardless of weight.   &lt;/CATEGORY_DESCRIPTION&gt;
      &lt;UPDATED_WHEN&gt;2003-08-20 12:00:57&lt;/UPDATED_WHEN&gt;
  &lt;/RESUSCITATION_WEIGHT_CATEGORY&gt;
  &lt;RESUSCITATION_WEIGHT_CATEGORY&gt;
      &lt;PATIENT_WEIGHT_CATEGORY&gt;01&lt;/PATIENT_WEIGHT_CATEGORY&gt;
      &lt;MINIMUM_WEIGHT&gt;0&lt;/MINIMUM_WEIGHT&gt;
      &lt;MAXIMUM_WEIGHT&gt;4&lt;/MAXIMUM_WEIGHT&gt;
      &lt;CATEGORY_DESCRIPTION&gt;Patients less than 4 Kg.&lt;/CATEGORY_DESCRIPTION&gt;
      &lt;UPDATED_WHEN&gt;2003-08-20 12:01:23&lt;/UPDATED_WHEN&gt;
  &lt;/RESUSCITATION_WEIGHT_CATEGORY&gt;
  &lt;RESUSCITATION_WEIGHT_CATEGORY&gt;
      &lt;PATIENT_WEIGHT_CATEGORY&gt;02&lt;/PATIENT_WEIGHT_CATEGORY&gt;
      &lt;MINIMUM_WEIGHT&gt;4&lt;/MINIMUM_WEIGHT&gt;
      &lt;MAXIMUM_WEIGHT&gt;9999&lt;/MAXIMUM_WEIGHT&gt;
      &lt;CATEGORY_DESCRIPTION&gt;Patients greater than 4 Kg.&lt;/CATEGORY_DESCRIPTION&gt;
      &lt;UPDATED_WHEN&gt;2003-08-20 12:01:52&lt;/UPDATED_WHEN&gt;
  &lt;/RESUSCITATION_WEIGHT_CATEGORY&gt;
  &lt;RESUSCITATION_WEIGHT_CATEGORY&gt;
      &lt;PATIENT_WEIGHT_CATEGORY&gt;03&lt;/PATIENT_WEIGHT_CATEGORY&gt;
      &lt;MINIMUM_WEIGHT&gt;0&lt;/MINIMUM_WEIGHT&gt;
      &lt;MAXIMUM_WEIGHT&gt;5&lt;/MAXIMUM_WEIGHT&gt;
      &lt;CATEGORY_DESCRIPTION&gt;Patients less than 5 Kg.&lt;/CATEGORY_DESCRIPTION&gt;
      &lt;UPDATED_WHEN&gt;2007-10-18 13:46:43&lt;/UPDATED_WHEN&gt;
  &lt;/RESUSCITATION_WEIGHT_CATEGORY&gt;
  &lt;RESUSCITATION_WEIGHT_CATEGORY&gt;
      &lt;PATIENT_WEIGHT_CATEGORY&gt;04&lt;/PATIENT_WEIGHT_CATEGORY&gt;
      &lt;MINIMUM_WEIGHT&gt;5&lt;/MINIMUM_WEIGHT&gt;
      &lt;MAXIMUM_WEIGHT&gt;9999&lt;/MAXIMUM_WEIGHT&gt;
      &lt;CATEGORY_DESCRIPTION&gt;Patients greater than 5 Kg.&lt;/CATEGORY_DESCRIPTION&gt;
      &lt;UPDATED_WHEN&gt;2007-10-18 13:47:23&lt;/UPDATED_WHEN&gt;
  &lt;/RESUSCITATION_WEIGHT_CATEGORY&gt;
  </code></pre>
  
  <p></p>
  
  <pre><code>&lt;?xml version='1.0'  encoding='UTF-8' ?&gt;
  &lt;RESUSCITATION_INFUSION_DRUGS&gt;
  &lt;RESUSCITATION_INFUSION_DRUG&gt;
      &lt;DRUG_NAME&gt;DOBUTamine&lt;/DRUG_NAME&gt;
      &lt;PATIENT_WEIGHT_CATEGORY&gt;07&lt;/PATIENT_WEIGHT_CATEGORY&gt;
      &lt;DRUG_CONCENTRATION&gt;12.5&lt;/DRUG_CONCENTRATION&gt;
      &lt;DRUG_CONCENTRATION_UNITS&gt;mg/mL&lt;/DRUG_CONCENTRATION_UNITS&gt;
      &lt;DOSAGE_RATE&gt;1&lt;/DOSAGE_RATE&gt;
      &lt;DOSAGE_RATE_UNITS&gt;mL/h&lt;/DOSAGE_RATE_UNITS&gt;
      &lt;DOSAGE_RATIO&gt;2&lt;/DOSAGE_RATIO&gt;
      &lt;DOSAGE_RATIO_UNITS&gt;mcg/kg/min&lt;/DOSAGE_RATIO_UNITS&gt;
      &lt;SOLUTION_PREPARATION&gt;&lt;/SOLUTION_PREPARATION&gt;
      &lt;SOLUTION_UNITS&gt;mg in 50 mL D5W&lt;/SOLUTION_UNITS&gt;
      &lt;VOLUME_UNITS&gt;mL&lt;/VOLUME_UNITS&gt;
      &lt;SOLUTION_CONCENTRATION&gt;&lt;/SOLUTION_CONCENTRATION&gt;
      &lt;DRUG_VOLUME&gt;&lt;/DRUG_VOLUME&gt;
  &lt;/RESUSCITATION_INFUSION_DRUG&gt;
  &lt;RESUSCITATION_INFUSION_DRUG&gt;
      &lt;DRUG_NAME&gt;DOPamine&lt;/DRUG_NAME&gt;
      &lt;PATIENT_WEIGHT_CATEGORY&gt;03&lt;/PATIENT_WEIGHT_CATEGORY&gt;
      &lt;DRUG_CONCENTRATION&gt;3.2&lt;/DRUG_CONCENTRATION&gt;
      &lt;DRUG_CONCENTRATION_UNITS&gt;mg/mL&lt;/DRUG_CONCENTRATION_UNITS&gt;
      &lt;DOSAGE_RATE&gt;&lt;/DOSAGE_RATE&gt;
      &lt;DOSAGE_RATE_UNITS&gt;mL/h&lt;/DOSAGE_RATE_UNITS&gt;
      &lt;DOSAGE_RATIO&gt;5&lt;/DOSAGE_RATIO&gt;
      &lt;DOSAGE_RATIO_UNITS&gt;mcg/kg/min&lt;/DOSAGE_RATIO_UNITS&gt;
      &lt;SOLUTION_PREPARATION&gt;160&lt;/SOLUTION_PREPARATION&gt;
      &lt;SOLUTION_UNITS&gt;mg in 50 mL D5W&lt;/SOLUTION_UNITS&gt;
      &lt;VOLUME_UNITS&gt;mL&lt;/VOLUME_UNITS&gt;
      &lt;SOLUTION_CONCENTRATION&gt;3200&lt;/SOLUTION_CONCENTRATION&gt;
      &lt;DRUG_VOLUME&gt;&lt;/DRUG_VOLUME&gt;
  &lt;/RESUSCITATION_INFUSION_DRUG&gt;
  </code></pre>
  
  <p></p>
  
  <p>I want to join the two xml files on the PATIENT_WEIGHT_CATEGORY field.</p>
  
  <p>Thanks!</p>