PHPCore PHPCore - 1 year ago 53
SQL Question

Join 2 tables, display one column and group by same column

I have 2 tables:

  1. [CompanyGroups] (id,id_company,id_group)

  2. [CompanyRegions] (id,id_company,id_region)

I want to select only id_company where (id_group in (...) or id_region in (..))

Select g.id_company, r.id_company
from CompanyGroups g, CompanyRegions r
where (g.id_group in (...) or r.id_region in (...)) group by id_company

The results are in 2 columns: g.id_company has some ids and r.id_company has others ids.

How to grup them in only 1 column?

Answer Source

I learnt Database concept quite long ago. So I try to recall those concept and answer what I remember to your question.

You are joining two tables without specifying any joining relationships between them. Then you select fields from each table, so the MySQL will join the two table by mapping each record from CompanyGroups to each record from CompanyRegions.

To solve your problem, you should use UNION instead.

SELECT id_company
FROM CompanyGroups 
WHERE id_group in (...)


SELECT id_company 
FROM CompanyRegions
WHERE id_region in (...)