I have three tables related to each other within a larger MySQL DB: ticket, ticket_generic_data (which is a passing table with an additional "value" attribute) and generic. I am specifically interested in tickets with a specific generic_id indicating that they have been sent to an app that processes surveys; furthermore, if the shipment has been processed correctly, that "value" I was talking about takes a 1 and if not, a 0.
What I want to do is a CASE, so that if the ticket has the proper id (let's say 3) and value 1, return "correct"; if generic_id is 3 and value 0, "failed"; and if the ticket does not correspond to that generic_id, "not sent".
The problem is that the same ticket can have different generic_id (or even not have any), which refer to different parameters, so if I do the CASE like this:
SELECT a.id AS ticket_id, b.generic_id, b.value,
CASE
WHEN generic_id = 3 AND value = 1 THEN 'Correcto'
WHEN generic_id = 3 AND value = 0 THEN 'Fallido'
ELSE 'No enviado'
END AS envio
FROM ticket AS a LEFT JOIN ticket_generic_data AS b ON a.id = b.ticket_id
LEFT JOIN generic AS c ON b.generic_id = c.id;
I get more records than tickets, since some tickets are both unsubmitted (when they have another generic_id other than 3) and success/fail. For example:
ticket_id generic_id value envio
1 1 0 No enviado
1 3 1 Correcto
2 3 0 Fallido
3 2 0 No enviado
3 4 0 No enviado
4 1 0 No enviado
4 2 0 No enviado
4 3 1 Correcto
5 NULL 0 No enviado
I guess this needs to be broken down into subqueries, but it's a topic I'm really green on and I don't know where to start. Let's see if you can guide me.
Greetings and thank you.
Conceptually you have two different states, but to understand it it is a bit easier to see it as a single state with 3 possibilities:
The tickets that meet the first 2 values are all those that have
generic_id = 3
, that is, those sent, the next value can be seen as all the tickets that are not in the previous set. Thinking about it this way, it is easier to formulate the solution in SQL.A colleague at work came up with a much simpler solution: