CODEWITHSUNDEEP
mysqlduplicatessubquery
Aitor Ramos Pajares
Aitor Ramos Pajares

Reputation: 361

show duplicate values subquery mysql

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

Upvotes: 2

Views: 243

Answers (3)

spencer7593
spencer7593

Reputation: 108460

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.

Upvotes: 2

Aitor Ramos Pajares
Aitor Ramos Pajares

Reputation: 361

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

Upvotes: 1

Rahul
Rahul

Reputation: 77896

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)

Upvotes: 0

Related Questions