Inner Join, multiple Inserts with multiple conditions

Question

Well, the thing is, I don’t understand where I should start, if I should select customers first, anyway, I’m sure I’ll use inner join or some joiner because it uses other help tables. I wanted help to understand how to do this query. Follows in the image the description of what should be done.

Follows the structure of the tables:

• tblTicket

  • Idticket (primary key)
  • Idcliente(foreign key)
  • Idevento (foreign key)
  • date(date)
  • stagePacking(bool)

• tblClientes

  • Idcliente
  • Sex
  • Name

• tblEvents

  • Idevents
  • Name

Answer 1

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

I’m guessing all primary keys are IDENTITY.

The basic insertion syntax is:

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

Notice I didn’t put IdTicket. For being a column IDENTITY, I don’t need to mention it in the sentence.

I could do that too:

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

The exercise says:

Each customer purchased one or two tickets for each of the Bookstore’s 20 events, being:

• A ticket if the IdCliente (sic) for par;

• Two tickets in case the IdCliente (sic) is odd;

The easy way to know if a number is even or odd is divined by 2 and checking if the rest of the division is zero. If so, it is even. Otherwise it is odd.

That is to say:

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

Thus, a SELECT to bring all customers with IdCliente par would be:

SELECT *
from tblClientes
where IdCliente % 2 = 0;

And the odd:

SELECT *
from tblClientes
where IdCliente % 2 = 1;

But the exercise calls for something that I see as meaningless (two lines for each IdCliente odd). In this case, a UNION ALL resolve by duplicating the lines:

SELECT *
from tblClientes
where IdCliente % 2 = 1

UNION ALL

SELECT *
from tblClientes
where IdCliente % 2 = 1

This isn’t very pretty or performative to do, but it works.

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

This I think is the simplest:

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

Only female customers have already made the payment of tickets.

This is an application of the sentence CASE:

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

Put it all together, it would be 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.