show duplicate values subquery mysql

Question

I have a mySQL query with a subquery. this subquery:

(SELECT r.tlf_reserva 
 FROM eventos e 
    INNER JOIN Reservas r 
    INNER JOIN viajes v 
 WHERE r.id_viaje=v.id_propia AND e.id=1) 

returns two values, but the query:

SELECT nombre 
FROM tblRegistration 
WHERE tlf= ALL(
             SELECT r.tlf_reserva 
             FROM eventos e 
              INNER JOIN Reservas r 
              INNER JOIN viajes v 
             WHERE r.id_viaje=v.id_propia 
             AND e.id=1) 

returns one value.

the two values are duplicate but I want to show them in the second query

Answer 1

No, since both the values are same (duplicates) and you are selecting single field; only one occurrence will be shown. If you still forcefully want to show then, you can use UNION ALL like

SELECT nombre 
FROM tblRegistration 
WHERE tlf = ALL(SELECT r.tlf_reserva 
FROM eventos e 
INNER JOIN Reservas r 
INNER JOIN viajes v 
WHERE r.id_viaje=v.id_propia 
AND e.id=1)

UNION ALL

SELECT nombre 
FROM tblRegistration 
WHERE tlf = ALL(SELECT r.tlf_reserva 
FROM eventos e 
INNER JOIN Reservas r 
INNER JOIN viajes v 
WHERE r.id_viaje=v.id_propia 
AND e.id=1)

Answer 2

I have found the solution, here is the correctly query:

SELECT nombre FROM tblRegistration a INNER JOIN(SELECT r.tlf_reserva FROM eventos e INNER JOIN Reservas r INNER JOIN viajes v WHERE r.id_viaje=v.id_propia AND e.id=1) b where a.tlf=b.tlf_reserva

Answer 3

Use join operation

  SELECT t.nombre 
    FROM tblRegistration t 
    JOIN ( SELECT r.tlf_reserva 
             FROM eventos e
            CROSS 
             JOIN Reservas r
             JOIN viajes v 
               ON v.id_propia = r.id_viaje
            WHERE e.id=1
         ) v
      ON t.tlf = v.tlf_reserva

Best practice is to qualify all column references, and put join predicates in ON clause rather than WHERE.