Select records based on two different values for the same field

Question

Select records based on two different values for the same field

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have this table and I want you to select the lots as long as they have a start and end, not just one of the two.

I tried with a and but I was not selected at all since I think that with that sentence I waited for a single field to meet the two criteria:

select Lote From hen.lote where Etapa ='Inicio' and 'Fin'

select Lote From hen.lote where Etapa ='Inicio' and Etapa='Fin';

mysql-workbench

asked by rolyx22 17.04.2018 в 20:46

source

2 answers

open a pdf on android with an application already installed PHP associative arrays

score 1 · Answer 1

1

If lots can have a single entry and exit, you can use this query:

SELECT Lote
FROM hen.lote
GROUP BY Lote
HAVING COUNT(Lote) > 1;

answered by 17.04.2018 в 21:00

score 1 · Answer 2

With this query, you have the values of "Start" and "End" appear to the side, as columns, and so you can find out which have their respective stages recorded or not:

SELECT L.lote, Inicio, Fin
FROM lote L
  LEFT JOIN (SELECT lote, etapa AS Inicio FROM lote WHERE etapa = 'Inicio') ini ON L.lote = ini.lote
  LEFT JOIN (SELECT lote, etapa AS Fin FROM lote WHERE etapa = 'Fin') fin ON L.lote = fin.lote
GROUP BY L.lote

The result of this is something like:

lote    Inicio  Fin
------------------------
t-001   Inicio  Fin
t-002   Inicio  (null)

This I tried on SQLFiddle .

Already playing with the same query, you can do the where you need, and in fact manipulate the value of the "columns" Start and End (in my example, the real values that the column "stage" brings, but you for example, you could give it a 1, as to make it more "Boolean", etc.: p).