CODEWITHSUNDEEP
mysqljoinsubqueryleft-join
fsulser
fsulser

Reputation: 904

sql left join limit subquery

I'm trying to get for each element of "spielplan" only one element (the last one) of "live_spiele". This is the whole query:

SELECT live.resh as l_resh, live.resa as l_resa, s.spielid, s.datum, s.zeit
       , s.ort, t.name, t.kurzname, s.gegner, s.resh, s.resa 
       from spielplan s
          LEFT JOIN (Select l.livespiel_id, l.resh, l.resa, l.spiel_id from live_spiele l) 
          live on s.spielid = live.spiel_id
       , teams t
       WHERE DATE(s.datum) BETWEEN '".$date1."' AND '".$date2."' 
       AND s.teamid = t.teamid 
       ORDER BY datum, zeit

but this is giving me more than 1 row for a element in "spielplan", if there are multiple rows is "live_spiele" with the same identifier.

What i tried was MIN/MAX in the subquery, but this doesn't helps, because the MIN/MAX value is not generally the right one for the identifier. I also tried with a LIMIT, but I don't get the solution.

Is there a way select only "live_spielplan" where the identifier is the same as "spielplan s" identifier?

Upvotes: 1

Views: 362

Answers (1)

Joke_Sense10
Joke_Sense10

Reputation: 5402

Use GROUP BY clause:

SELECT live.resh as l_resh, live.resa as l_resa, s.spielid, s.datum, s.zeit
   , s.ort, t.name, t.kurzname, s.gegner, s.resh, s.resa 
from spielplan s
LEFT JOIN (Select l.livespiel_id, l.resh, l.resa, l.spiel_id from live_spiele l) 
live 
on s.spielid = live.spiel_id
, teams t
WHERE DATE(s.datum) BETWEEN '".$date1."' AND '".$date2."' 
AND s.teamid = t.teamid 
ORDER BY datum, zeit GROUP BY s.spiel_id

Upvotes: 1

Related Questions