Posted by Hilarion on 10/20/05 17:45

> 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 = ??


Try this:

SELECT
c.company_id,
c.company_name,
v.variable_id,
v.variable_name,
vv.variable_value
FROM
company AS c CROSS JOIN
variable AS v LEFT OUTER JOIN
variable_value AS vv ON vv.company_id = c.company_id AND v.variable_id = vv.variable_id
[
WHERE
c.company_id = ??
]
ORDER BY
c.company_name,
c.company_id,
v.variable_name,
v.variable_id,
vv.variable_value

The part marked with "[]" (WHERE clause) is optional.


Hilarion

[Back to original message]