Inner Join, multiple Inserts with multiple conditions

Navigation
1

Well, it's the following, I do not understand where I should start, if I must first select the clients, I'm sure I'll use inner join or some joiner since it uses other help tables. I wanted help to understand how to do this query. Here is a description of what should be done in the image.

The structure of the tables follows:

  • tblTicket

    • IdTicket (primary key)
    • CustomerID (foreign key)
    • IdEvent (foreign key)
    • data (date)
    • statusPagamendo (bool)

  • tblCustomers

    • CustomerID
    • Sex
    • Name

  • tblEvents

    • IdEventos
    • Name

    
asked by anonymous 10.03.2015 / 13:48

1 answer

1

It seems to me to be a database exercise, even because what the statement proposes does not make much sense.

I'm guessing that all primary keys are IDENTITY .

The basic insert syntax is: 

INSERT INTO tblTickets (IdCliente, IdEvento, data, estadoPagamento)
SELECT ...

Notice that I did not IdTicket . Because it is a IDENTITY column, I do not need to mention it in the sentence.

I could also do this: 

INSERT INTO tblTickets (IdTicket, IdCliente, IdEvento, data, estadoPagamento)
SELECT null, ...

The exercise says:

  

Each customer has purchased one or two tickets for each of the 20 Livraria events, including:

     

  • A ticket if IdCliente (sic) is even;

    •   

  • Two tickets if the IdCliente (sic) is odd;

    •   

The easy way to know whether a number is even or odd is by dividing by 2 and checking to see if the rest of the division is zero. If it is, it's even. Otherwise, it is odd.

That is: 

SELECT 6 % 2; -- Devolve 0
SELECT 7 % 2; -- Devolve 1

So a SELECT to bring all clients with IdCliente pair would be: 

SELECT *
from tblClientes
where IdCliente % 2 = 0;

And the odd ones: 

SELECT *
from tblClientes
where IdCliente % 2 = 1;

Except that the exercise asks for something that I see as meaningless (two rows for each IdCliente odd). In this case, a UNION ALL resolves by duplicating the lines: 

SELECT *
from tblClientes
where IdCliente % 2 = 1

UNION ALL

SELECT *
from tblClientes
where IdCliente % 2 = 1

This is not very beautiful or performatic to do, but it works.

  

The DataDaVenda field must be 01/03/2009 in all records.

This one I think is the simplest: 

SELECT CONVERT(DATETIME, '01/03/2009', 103);
  

Only female customers have already paid for the tickets.

This is an application of the CASE : 

CASE 
    WHEN SEXO = 'M' THEN TRUE
    ELSE FALSE
END

Putting it all together, it would look something like this: 

INSERT INTO tblTickets (IdCliente, IdEvento, data, estadoPagamento)
SELECT c.IdCliente, e.IdEvento, CONVERT(DATETIME, '01/03/2009', 103), 
    (CASE
        WHEN c.Sexo = 'M' THEN TRUE
        ELSE FALSE
    END) as EstadoPagamento
FROM tblClientes c
CROSS JOIN tblEventos e
WHERE c.IdCliente % 2 = 0;

INSERT INTO tblTickets (IdCliente, IdEvento, data, estadoPagamento)
SELECT c.IdCliente, e.IdEvento, CONVERT(DATETIME, '01/03/2009', 103), 
    (CASE
        WHEN c.Sexo = 'M' THEN TRUE
        ELSE FALSE
    END) as EstadoPagamento
FROM tblClientes c
CROSS JOIN tblEventos e
WHERE c.IdCliente % 2 = 1

UNION ALL

SELECT c.IdCliente, e.IdEvento, CONVERT(DATETIME, '01/03/2009', 103), 
    (CASE
        WHEN c.Sexo = 'M' THEN TRUE
        ELSE FALSE
    END) as EstadoPagamento
FROM tblClientes c
CROSS JOIN tblEventos e
WHERE c.IdCliente % 2 = 1;

Finally, CROSS JOIN performs a Cartesian product between the two related tables.

    
10.03.2015 / 16:50
Relationship between tables in MySQL Datepicker does not work