Multiple MySQL JOINs and duplicated cells
<p>I have two MySQL queries that give the results that I'm looking for. I'd ultimately like to combine these two queries to produce a single table and I'm stuck.</p>

<p>QUERY 1:</p>

<pre><code>SELECT scoc.isr, outcome_concept_id, concept_name as outcome_name
FROM standard_case_outcome AS sco INNER JOIN concept AS c
ON sco.outcome_concept_id = c.concept_id
INNER JOIN standard_case_outcome_category AS scoc
ON scoc.isr = sco.isr
WHERE scoc.outc_code = 'CA'
</code></pre>

<p>RESULT 1:</p>

<p><a href="https://i.stack.imgur.com/ZEoRc.png" rel="nofollow"><img src="https://i.stack.imgur.com/ZEoRc.png" alt="RESULT OF QUERY 1 (TRUNCATED)"></a></p>

<p>QUERY 2:</p>

<pre><code>SELECT scoc.isr, drug_seq, concept_id, concept_name as drug_name
FROM standard_case_drug AS scd INNER JOIN concept AS c
ON scd.standard_concept_id = c.concept_id
INNER JOIN standard_case_outcome_category AS scoc
ON scoc.isr = scd.isr
WHERE scoc.outc_code = 'CA'
</code></pre>

<p>RESULT 2:</p>

<p><a href="https://i.stack.imgur.com/zjJDM.png" rel="nofollow"><img src="https://i.stack.imgur.com/zjJDM.png" alt="RESULT OF QUERY 2 (TRUNCATED)"></a></p>

<p>DESIRED RESULT:
<a href="https://i.stack.imgur.com/MKlMJ.png" rel="nofollow"><img src="https://i.stack.imgur.com/MKlMJ.png" alt="DESIRED RESULT"></a></p>

<p>I'm pretty sure I can figure out how to do it using Python/pandas, but I was wondering if there is (a) a way to do this MySQL (b) any benefit to doing it with MySQL.</p>

<p>** If you're curious, <a href="http://datadryad.org/resource/doi:10.5061/dryad.8q0s4" rel="nofollow">this is the entire dataset</a>.</p>

<p>Here's the db structure for the pertinent tables:</p>

<pre><code># Dump of table concept
# ------------------------------------------------------------

CREATE TABLE `concept` (
  `concept_id` int(11) NOT NULL,
  `concept_name` varchar(255) NOT NULL,
  `domain_id` varchar(20) NOT NULL,
  `vocabulary_id` varchar(20) NOT NULL,
  `concept_class_id` varchar(20) NOT NULL,
  `standard_concept` varchar(1) DEFAULT NULL,
  `concept_code` varchar(50) NOT NULL,
  `valid_start_date` date NOT NULL,
  `valid_end_date` date NOT NULL,
  `invalid_reason` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`concept_id`),
  UNIQUE KEY `idx_concept_concept_id` (`concept_id`),
  KEY `idx_concept_code` (`concept_code`),
  KEY `idx_concept_vocabluary_id` (`vocabulary_id`),
  KEY `idx_concept_domain_id` (`domain_id`),
  KEY `idx_concept_class_id` (`concept_class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table standard_case_drug
# ------------------------------------------------------------

CREATE TABLE `standard_case_drug` (
  `primaryid` varchar(512) DEFAULT NULL,
  `isr` varchar(512) DEFAULT NULL,
  `drug_seq` varchar(512) DEFAULT NULL,
  `role_cod` varchar(512) DEFAULT NULL,
  `standard_concept_id` int(11) DEFAULT NULL,
  KEY `idx_standard_case_drug_primary_id` (`primaryid`(255),`drug_seq`(255)),
  KEY `idx_standard_case_drug_isr` (`isr`(255),`drug_seq`(255)),
  KEY `idx_standard_case_drug_standard_concept_id` (`standard_concept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table standard_case_outcome
# ------------------------------------------------------------

CREATE TABLE `standard_case_outcome` (
  `primaryid` varchar(512) DEFAULT NULL,
  `isr` varchar(512) DEFAULT NULL,
  `pt` varchar(512) DEFAULT NULL,
  `outcome_concept_id` int(11) DEFAULT NULL,
  `snomed_outcome_concept_id` int(11) DEFAULT NULL,
  KEY `idx_standard_case_outcome_primary_id` (`primaryid`(255)),
  KEY `idx_standard_case_outcome_isr` (`isr`(255)),
  KEY `idx_standard_case_outcome_outcome_concept_id` (`outcome_concept_id`),
  KEY `idx_standard_case_outcome_snomed_outcome_concept_id` (`snomed_outcome_concept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table standard_case_outcome_category
# ------------------------------------------------------------

CREATE TABLE `standard_case_outcome_category` (
  `primaryid` varchar(512) DEFAULT NULL,
  `isr` varchar(512) DEFAULT NULL,
  `outc_code` varchar(512) DEFAULT NULL COMMENT 'Code for a patient outcome (See table below) CODE MEANING_TEXT ----------------DE Death LT Life-ThreateningHO Hospitalization - Initial or ProlongedDS DisabilityCA Congenital AnomalyRI Required Intervention to Prevent PermanentImpairment/DamageOT Other Serious (Important Medical Event) NOTE: The outcome from the latest version of a case is provided. If there is more than one outcome, the codes willbe line listed.',
  `snomed_concept_id` int(11) DEFAULT NULL,
  KEY `idx_standard_case_outcome_category_primary_id` (`primaryid`(255)),
  KEY `idx_standard_case_outcome_category_isr` (`isr`(255)),
  KEY `idx_standard_case_outcome_category_snomed_concept_id` (`snomed_concept_id`,`outc_code`(255))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
</code></pre>

<p>This should get you the desired results.</p>

<pre><code>SELECT
    `scoc`.`isr`                AS `isr`,
    `sco` .`outcome_concept_id` AS `outcome_concept_id`,
    `c1`  .`concept_name`       AS `outcome_name`,
    `scd` .`drug_seq`           AS `drug_seq`,
    `scd` .`concept_id`         AS `concept_id`,
    `c2`  .`concept_name`       AS `drug_name`
FROM
    `standard_case_outcome` AS `sco`
INNER JOIN
    `concept` AS `c1`
    ON
        `sco`.`outcome_concept_id` = `c1`.`concept_id`
LEFT JOIN
    `standard_case_drug` AS `scd`
    ON
        `sco`.`isr` = `scd`.`isr`
INNER JOIN
    `concept` AS `c2`
    ON
        `scd`.`outcome_concept_id` = `c2`.`concept_id`
INNER JOIN
    `standard_case_outcome_category` AS `scoc`
    ON
        `scoc`.`isr` = `sco`.`isr`
WHERE
    `scoc`.`outc_code` = 'CA'
</code></pre>

<p><strong>EDIT</strong></p>

<p>Note that I left out the <code>concept</code> table, as you are not selecting anything from it, or filtering the results with it.</p>

<p><strong>SECOND EDIT</strong></p>

<p>Updated to include the <code>concept</code> table. Updated question showed that it is in fact needed in the <code>SELECT</code>.</p>

<p><strong>THIRD EDIT</strong></p>

<p>Needs to select <code>concept</code>.<code>name</code> for <code>sco</code>, and <code>scd</code> respectively.</p>

python mysql sql pandas