SQL Query Inner joins with 3 tables

Benpsycho

Hi,

I have the following situation:

3 tables:

Table: variable_value
variable_id int(11)
company_id int(11)
variable_value varchar(255)

Table: variable
variable_id int(11)
variable_name varchar(255)

Table: company
company_id int(11)
company_name varchar(255)

I store variables in the variable tables and companies in the company table. I want to assign a different value to each variable for each company. Now I need a select that will show me all variables of a company if they are in the variable_value table yet or not.

I came this far, but this isn't what I need, since it does not show all variables:
Select variable_value, variable.variable_id, variable_name, company_name FROM company
LEFT JOIN variable_value ON variable_value.company_id = company.company_id

LEFT JOIN variable ON variable.variable_id = variable_value.variable_id WHERE company_id = ??

Anyone got an idea?

Thx alot

Benpsycho

I get the following results from the query:

variable_value NULL
variable_id NULL
variable_name NULL
company_name samplecompany

eventhough I have like 3 variables in variable_name

So I would like the following (if variable_value would be empty):
variable_value NULL

variable_id 1

variable_name sampplevar1

company_name samplecompany

variable_value NULL

variable_id 2

variable_name sampplevar2

company_name samplecompany

variable_value NULL

variable_id 3

variable_name sampplevar3

company_name samplecompany

Thx for your help.

Benpsycho

Heres a data dump:


CREATE TABLE `company` (
  `company_id` int(11) NOT NULL auto_increment,
  `company_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`company_id`),
  UNIQUE KEY `company_name` (`company_name`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

-- 
-- Daten für Tabelle `company`
-- 

INSERT INTO `company` VALUES (1, 'samplecompany');

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

-- 
-- Tabellenstruktur für Tabelle `variable`
-- 

CREATE TABLE `variable` (
  `variable_id` int(11) NOT NULL auto_increment,
  `variable_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`variable_id`),
  UNIQUE KEY `variable_name` (`variable_name`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

-- 
-- Daten für Tabelle `variable`
-- 

INSERT INTO `variable` VALUES (1, 'sample_var1');
INSERT INTO `variable` VALUES (2, 'sample_var2');

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

-- 
-- Tabellenstruktur für Tabelle `variable_value`
-- 

CREATE TABLE `variable_value` (
  `variable_id` int(11) NOT NULL default '0',
  `company_id` int(11) NOT NULL default '0',
  `variable_value` varchar(255) NOT NULL default ''
) TYPE=MyISAM;