CODEWITHSUNDEEP
mysqlsqlselectassociations
Hannes Schmid
Hannes Schmid

Reputation: 389

MySQL: Select via association

I have the following MySQL tables:

Table company containg the following attributes (about 250 rows):

Table event containg the following attributes (about 20 rows):

Cross table cpy_evt (about 550 rows) defines what company is involved in what event. The table contains one row per association between a company and an event. The table contains the following attributes:

Due to my limited SQL knowledge I have the following questions.

How can I select:

  1. All companies (all attributes) that are involved in event A
  2. All companies that are involved in event A AND event B
  3. All companies that are involved in event A OR event B
  4. All companies that are not involved in any event

Thanks a lot for your help! Hannes

Upvotes: 0

Views: 274

Answers (1)

GMB
GMB

Reputation: 222432

  1. All companies (all attributes) that are involved in event A

Without knowing your data, I would go for the safer approach to avoid potential duplicates : an EXISTS condition with a correlated subquery.

SELECT c.*
FROM company c
WHERE EXISTS (
    SELECT 1
    FROM company_event ce
    INNER JOIN event e ON e.name_evt = 'A' AND e.id_evt = ce.id_evt_crs
    WHERE ce.id_cpy_crs = c.id_cpy
)
  1. All companies that are involved in event A AND event B

You can add an additional EXISTS condition :

SELECT c.*
FROM company c
WHERE EXISTS (
    SELECT 1
    FROM company_event ce
    INNER JOIN event e ON e.name_evt = 'A' AND e.id_evt = ce.id_evt_crs
    WHERE ce.id_cpy_crs = c.id_cpy
)
AND EXISTS (
    SELECT 1
    FROM company_event ce
    INNER JOIN event e ON e.name_evt = 'B' AND e.id_evt = ce.id_evt_crs
    WHERE ce.id_cpy_crs = c.id_cpy
)
  1. All companies that are involved in event A OR event B

Change the equality condition on event name to an IN condition.

SELECT c.*
FROM company c
WHERE EXISTS (
    SELECT 1
    FROM company_event ce
    INNER JOIN event e ON e.name_evt IN ('A', 'B') AND e.id_evt = ce.id_evt_crs
    WHERE ce.id_cpy_crs = c.id_cpy
)
  1. All companies that are not involved in any event

This simplifies the subquery, since we do not need to join the event table.

SELECT c.*
FROM company c
WHERE NOT EXISTS (
    SELECT 1
    FROM company_event ce
    WHERE ce.id_cpy_crs = c.id_cpy
)

Upvotes: 1

Related Questions