Hello
I have the following table structure
table t_companies with fields name and company_id
a table t_company_label with company_id and label_id (each company can have multiple labels)
a table ref_labels with the fields label_id and label_name
now I want to get all company_names with the label_name=XXXX,
and if XXX is empty I want all companies from the table.
When I try to conduct it with the query designer, access complains that there are outer joins which should be included in subqueries.
so I tried:
It works fine if XXX is not empty.
However the problem is that I will only get the companies with a record in t_company_label when XXX is emtpy, but I want to get all.
How to achieve this?
Thanks
Florian
I have the following table structure
table t_companies with fields name and company_id
a table t_company_label with company_id and label_id (each company can have multiple labels)
a table ref_labels with the fields label_id and label_name
now I want to get all company_names with the label_name=XXXX,
and if XXX is empty I want all companies from the table.
When I try to conduct it with the query designer, access complains that there are outer joins which should be included in subqueries.
so I tried:
Code:
SELECT t_companies.name
FROM t_companies
WHERE t_companies.companyID IN
(SELECT t_company_label.company_ID
FROM ref_label INNER JOIN t_company_label ON (t_company_label.label_ID = ref_label.labelID) WHERE (((ref_label.label) LIKE '*' & 'XXX' & '*' )))It works fine if XXX is not empty.
However the problem is that I will only get the companies with a record in t_company_label when XXX is emtpy, but I want to get all.
How to achieve this?
Thanks
Florian
