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

score 2 · Answer 1 · answered Jun 07 '15 at 01:02

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.

score 1 · Answer 2 · answered Jun 07 '15 at 01:00

1

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

answered Jun 07 '15 at 01:00

Aitor Ramos Pajares

361
2
20

score 0 · Answer 3 · answered Jun 07 '15 at 00:56

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)

show duplicate values subquery mysql

3 Answers3