Help with complex join conditions

Question

I have the following mysql table schema:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `network`
--

-- --------------------------------------------------------

--
-- Table structure for table `contexts`
--

CREATE TABLE IF NOT EXISTS `contexts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `neurons`
--

CREATE TABLE IF NOT EXISTS `neurons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `synapses`
--

CREATE TABLE IF NOT EXISTS `synapses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `n1_id` int(11) NOT NULL,
  `n2_id` int(11) NOT NULL,
  `context_id` int(11) NOT NULL,
  `strength` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

What SQL can I write to get all Neurons associated with a specified context along with the sum of the strength column for the synapses associated with each neuron?

I'm using the following query, which returns the sum of the strength of the synapses associated with one neuron. I need to get information for all of the neurons:

/* This query finds how strongly the neuron with id 1 is connected to the context with keyword ice cream*/

SELECT SUM(strength) AS Strength FROM
synapses
JOIN contexts AS Context ON synapses.context_id = Context.id
JOIN neurons AS Neuron ON Neuron.id = synapses.n1_id OR Neuron.id = synapses.n2_id
WHERE Neuron.id = 1 AND Context.keyword = 'ice cream'

For example, that query returns one row, where Strength is 2. Ideally, I could have one column for the neurons.id, one for neurons.name, and one for SUM(synapses.strength) with one record for each distinct neuron.

Answer 1

Does this do what you want?

SELECT contexts.keyword, neurons.id, neurons.name, SUM(synapses.strength)
FROM neurons
INNER JOIN synapses ON neurons.id = synapses.n1_id OR neurons.id = synapses.n2_id
INNER JOIN contexts ON synapses.context_id = contexts.id
GROUP BY contexts.keyword, neurons.id, neurons.name

Answer 2

Use:

   SELECT DISTINCT 
          n.*,
          COALESCE(x.strength, 0) AS strength
     FROM NEURONS n
     JOIN SYNAPSES s ON n.id IN (s.n1_id, s.n2_id)
     JOIN CONTEXTS c ON c.id = s.context_id
LEFT JOIN (SELECT c.id AS c_id,
                  n.id AS n_id,
                  SUM(strength) AS Strength 
             FROM SYNAPSES s
             JOIN CONTEXTS c ON c.id = s.context_id
             JOIN NEURONS n ON n.id IN (s.n1_id, s.n2_id)
         GROUP BY c.id, n.id) x ON x.c_id = c.id
                               AND x.n_id = n.id