Query specific case condition

Question

As the title suggests, I came across a specific case, or both I describe it verbatim here and then report the logic of my tables. Example: We admit that there is an event, this event is attended by a customer, and this customer can have 1 store or more stores, participating in this event. So far nothing strange. On the events page I have a table that shows me the whole list of events ordered for my clients, and then if a person wants to consult the details page of that event, he can consult the stores of that client participating for that event and all the characteristics, but we remain on the page that shows me all the events associated with the customer.

My problem is this.

1. I create a customer
2. I create an event
3. I associate the stores with the customer for that event
4. I go to the events page
5. I can see everything
6. BUT if I delete from the customer table, the customer I registered at (item 1), the whole table relating to events will disappear.

Instead I would like that even if the customer is canceled, the event that is associated with that customer does not disappear, but was only lacking the data of the name and surname of that customer.

I took this trivial example to make clear what was my problem, now I report the tables and below the query

Customer Table's name: clienti

CREATE TABLE `clienti` (
  `cliente_id` int(11) NOT NULL,
  `nome` varchar(255) NOT NULL,
  `cognome` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `telefono` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The events table's name: campagne_cliente

CREATE TABLE `campagne_cliente` (
  `id_campagna_cliente` int(11) NOT NULL,
  `cliente_id_campagna` int(11) NOT NULL,
  `impianto_id_campagna` int(11) NOT NULL,
  `nome_campagna_cliente` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

where impianto_id_campagna are the ids of the products (shops)

The id and duration of the table event's name: campagne

CREATE TABLE `campagne` (
  `id_campagna` int(11) NOT NULL,
  `data_inizio` date NOT NULL,
  `data_fine` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The only part that I overlooked is that of shops (products) because I do not care was to understand as an example, the process is I have already created all my products.

I create a customer and I have the information in the clienti table

I create a start and end event and go to the table campagne

Associate the customer with one or more products and go to the table campagne_cliente

and show all with this query:

"SELECT DISTINCT nome_campagna_cliente
              , nome
              , cognome
              , telefono
              , email
              , cliente_id
              , data_inizio
              , data_fine
              , id_campagna_cliente
           FROM clienti 
           JOIN campagne_cliente 
             ON campagne_cliente.cliente_id_campagna = clienti.cliente_id
           JOIN campagne 
             ON campagne.id_campagna = campagne_cliente.id_campagna_cliente
             GROUP BY campagne.id_campagna"

Now I have a standard case in the campagne_cliente table

- id_campagna = id of event
- cliente_id_campagna = id of customer
- impianto_id_campagna = id of products

Practice example:

- id_campagna = 10
- cliente_id_campagna = 30
- impianto_id_campagna = 42

if I go to the clienti table and delete the customer id = 42

with the query I wrote above how do I still show the other information even if that customer with id 42 no longer exists, thanks

Answer 1

If you delete record from clienti, you'll need to write:

SELECT DISTINCT nome_campagna_cliente
              , nome
              , cognome
              , telefono
              , email
              , cliente_id
              , data_inizio
              , data_fine
              , id_campagna_cliente
           FROM clienti 
           RIGHT OUTER JOIN campagne_cliente 
             ON campagne_cliente.cliente_id_campagna = clienti.cliente_id
           RIGHT OUTER JOIN campagne 
             ON campagne.id_campagna = campagne_cliente.id_campagna_cliente
             GROUP BY campagne.id_campagna

While you are at it, you should have a look into foreign key (especially things like ON DELETE SET NULL) here.

Cheers