CODEWITHSUNDEEP
mysql
kurtko
kurtko

Reputation: 2126

MySQL: WHERE IN with JOIN inside

I have 3 SQL tables.

Games wo_tenis_partidos

enter image description here

Selections wo_tenis_pronosticos

enter image description here

Quotas wo_tenis_cuotas_ha2

enter image description here

I need to get all rows with two conditions:

Condition 1: where CUOTA1 >= CUOTAMIN1 or CUOTA2 >= CUOTAMIN2

Condition 2: Add all rows with same ID_PARTIDO that appeared in Condition 1

The condition 1 is easy:

SELECT *               
FROM wo_tenis_partidos a
INNER JOIN wo_tenis_cuotas_ha2 c ON c.id_partido = a.id
INNER JOIN wo_tenis_pronosticos p ON p.id_partido = a.id
WHERE
a.resultado IS NULL 
AND (c.cuota1 >= p.cuotamin1 OR c.cuota2 >= p.cuotamin2)

But to add the condiciton 2 I need to do this:

SELECT *               
FROM wo_tenis_partidos a
INNER JOIN wo_tenis_cuotas_ha2 c ON c.id_partido = a.id
INNER JOIN wo_tenis_pronosticos p ON p.id_partido = a.id
WHERE a.id IN (
    SELECT a.id                 
    FROM wo_tenis_partidos a
    INNER JOIN wo_tenis_cuotas_ha2 c ON c.id_partido = a.id
    INNER JOIN wo_tenis_pronosticos p ON p.id_partido = a.id    
    AND a.resultado IS NULL 
    AND (c.cuota1 >= p.cuotamin1 OR c.cuota2 >= p.cuotamin2)                        
)

It's the correct way? there is another more optimized alternative?

Upvotes: 2

Views: 770

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

No need of INNER JOIN in first query and use EXISTS instead of IN clause for better performance

SELECT *               
FROM wo_tenis_partidos aa
INNER JOIN wo_tenis_cuotas_ha2 c ON c.id_partido = aa.id
INNER JOIN wo_tenis_pronosticos p ON p.id_partido = aa.id
WHERE EXISTS (
      SELECT 1                 
      FROM wo_tenis_partidos a
      INNER JOIN wo_tenis_cuotas_ha2 c ON c.id_partido = a.id
      INNER JOIN wo_tenis_pronosticos p ON p.id_partido = a.id    
      AND a.resultado IS NULL 
      AND (c.cuota1 >= p.cuotamin1 OR c.cuota2 >= p.cuotamin2) 
      WHERE aa.id =a.id                        
)

Upvotes: 2

Related Questions