title: MySQL query to get all (additional) symptoms and diseases
body: <p>I have a database which contains main symptoms, additional symptoms and diseases. I need to replace 2 queries because I am sure my first query is not efficient and my 2nd is not correct at all. I hope anyone can help me because I am new to this area..</p>
  
  <p><strong>Database explanation:</strong></p>
  
  <p>The database is being used by a medical app:</p>
  
  <ol>
  <li>The user selects a specific bodypart</li>
  <li>The app lists all main symptoms of that specific bodypart</li>
  <li>The user selects a  main symptom (common or less common)</li>
  <li><p>The app lists all diseases of the selected main symptom. There also appear 2 checkboxes (additional symptoms) that can be checked by the user. The order of the listed diseases (d_weight) depends on age, gender, selected main symptom and what boxes the user has checked. A disease with <code>d_weight</code> &lt;= 5 is considered to be a common disease. A disease with <code>d_weight</code> > 5 is considered to be less-common. The possibilities of user input (age, gender, bodypart, main symptom) is stored in the symptom_disease_combi table.</p>
  
  <ul>
  <li><code>asa_id</code> is the id of all symptom that apply (addtional symptoms that are checked by the user)</li>
  <li><code>asc_id</code> is the id of all possibilities of additional symptoms that belong to the specific main symptom. For example, <code>asc_id</code> = 0, in case there is no additional symptom selected. <code>asc_id</code> = 1, in case only additional symptom 'Insomnia' is selected. <code>asc_id</code> = 2, in case both 'Insomnia' and 'Blowing up' are selected.  </li>
  </ul></li>
  </ol>
  
  <p><strong>1. Query to get all symptoms of a specific bodypart (can be improved):</strong></p>
  
  <pre><code>SELECT DISTINCT s.name
                , s.id
                , sdc.s_common 
             FROM symptom as s
                , symptom_disease_combi as sdc 
            WHERE sdc.age = ".$age." 
              AND sdc.gender = ".$gender." 
              AND sdc.bodypart = ".$bodypart." 
              AND sdc.s_id = s.id
  </code></pre>
  
  <p><strong>2. Query to get all diseases and additional symptoms of selected symptom (doesn't work):</strong></p>
  
  <pre><code>SELECT DISTINCT d.name
                , d.id
                , sdc.d_weight
                , adls.id
                , adls.name 
             FROM disease as d 
                , symptom_disease_combi as sdc
                , symptom as s
                , adlsymptom as adls 
            WHERE sdc.age = ".$age." 
              AND sdc.gender = ".$gender." 
              AND sdc.bodypart = ".$bodypart." 
              AND s.id = ".$sid." 
              AND sdc.s_id = s.id
  </code></pre>
  
  <p><strong>3. Database structure (please let me know if my design can be improved)</strong></p>
  
  <pre><code>CREATE TABLE symptom
  (id INT NOT NULL AUTO INCREMENT
  ,name VARCHAR(100) DEFAULT NULL
  ,critical INT NOT NULL
  ,PRIMARY KEY (id)
  ) ENGINE=MyISAM;
  
  id name                    critical
   1 Behavioral disturbances        1
   2 Ear pressure                   0
   3 Elevated temperature           0
   4 Fainting                       0
   5 Head pain                      0
  
  CREATE TABLE disease (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) DEFAULT NULL,
  critical int(11) NOT NULL,
  description text NOT NULL,
  tests text NOT NULL,
  treatment text NOT NULL,
  PRIMARY KEY (id)
  ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1
  
  id name                critical    description    tests       treatment
   1 Adjustment disorder 0           lorem ipsum    lorem ipsum lorem ipsum
   2 ADHD                0           lorem ipsum    lorem ipsum lorem ipsum
   3 Drug reaction       0           lorem ipsum    lorem ipsum lorem ipsum
   4 Seizure (epilepsy)  1           lorem ipsum    lorem ipsum lorem ipsum        
  
  CREATE TABLE adlsymptom (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  PRIMARY KEY (id)
  ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
  
  id name
   1 Insomnia
   2 Blowing up
   3 Depressed
   4 Drug abuse
  
  CREATE TABLE adlsymptom_apply (
  id int(11) NOT NULL,
  as_id int(11) NOT NULL,
  PRIMARY KEY (id,as_id),
  KEY FK_additional_symptom_that_apply (as_id)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  
  id as_id
   1 1
   1 2
  
  CREATE TABLE adlsymptom_combi (
  id int(11) NOT NULL,
  as_id int(11) NOT NULL,
  PRIMARY KEY (id,as_id),
  KEY FK_additional_symptom_combination (as_id)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  
  id as_id
   1 1
   2 2
   3 1
   3 2
  
  CREATE TABLE symptom_disease_combi (
  id int(11) NOT NULL AUTO_INCREMENT,
  age int(11) NOT NULL,
  gender int(11) NOT NULL,
  bodypart int(11) NOT NULL,
  s_id int(11) NOT NULL,
  s_common int(11) NOT NULL,
  asc_id int(11) NOT NULL,
  asa_id int(11) NOT NULL,
  d_id int(11) NOT NULL,
  d_weight int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY FK_symptom (s_id),
  KEY FK_additional_symptom_combination (asc_id),
  KEY FK_additional_symptom_that_apply (asa_id),
  KEY FK_disease (d_id)
  ) ENGINE=MyISAM AUTO_INCREMENT=65 DEFAULT CHARSET=latin1
  
  id age gender bodypart s_id s_common asc_id asa_id d_id d_weight
   1 1   1      1        1    1        0      1      1    1
   2 1   1      1        1    1        0      1      2    2  
   3 1   1      1        1    1        0      1      3    3
   4 1   1      1        1    1        0      1      11   4
  </code></pre>
  
  <p><strong>UPDATE 1:</strong></p>
  
  <ul>
  <li><code>critical</code> in disease and symptom is created to tell the user that they need to go to the hospital immediately, when they click on the disease or symptom</li>
  <li><code>age</code>, <code>gender</code>, <code>bodypart</code> are id's, so <code>age</code> = 1, means 0-5, <code>age</code> = 2 means 6-17, <code>age</code> = 3 means 18-59 and <code>age</code> = 4 means 60+.</li>
  <li>Please take a look at the design of the application, it will help a lot to understand the design of the database: <a href="http://i.imgur.com/p9QP1av.png" rel="nofollow">http://i.imgur.com/p9QP1av.png</a> Btw, in the design; cause == disease...</li>
  <li><code>asa_id</code> refers to id of adlsymptom_apply</li>
  <li><code>asc_id</code> referst to id of adlsymptom_combi</li>
  <li>The 'distinct' was used to get all symptoms/diseases just 1 time. I am sure it's not needed, but I don't know how to fix it.</li>
  </ul>