CODEWITHSUNDEEP
mysql
Erik
Erik

Reputation: 11

Help Required in writing SQL Query

I have a query which I have found on Sopocosy sport feed documentation. I have change this query to meet my need. Here is my query

SELECT
        e.id as eid,
        ts.name AS stage_name,
        DATE_FORMAT(e.startdate, '%d.%m.%Y') AS startdate,
        DATE_FORMAT(e.startdate, '%H:%i') AS starttime,
        MIN(IF( ep.number = 1, p.name, NULL)) AS home_team,
        IF(e.status_type = 'notstarted', '-',(MIN(IF(ep.number = 1, r.value, NULL)))) AS home_score,
        IF(e.status_type = 'notstarted', '-',(MIN(IF(ep.number = 2, r.value, NULL)))) AS away_score,
        MIN(IF(ep.number = 2, p.name, NULL)) AS away_team,
        es.name AS status_text
FROM
        tournament_template AS tt INNER JOIN
        tournament AS t ON t.tournament_templateFK = tt.id INNER JOIN
        tournament_stage AS ts ON t.id = ts.tournamentFK INNER JOIN
        event AS e ON ts.id = e.tournament_stageFK INNER JOIN
        event_participants AS ep ON e.id = ep.eventFK LEFT JOIN
        status_desc AS es ON e.status_descFK = es.id LEFT JOIN
        participant AS p ON ep.participantFK = p.id LEFT JOIN
        result AS r ON ep.id = r.event_participantsFK AND r.result_code = 'runningscore' LEFT JOIN
        property AS prop ON e.id = prop.objectFK AND prop.object ='event' AND prop.name = 'Live'
WHERE
        tt.sportFK = '1'
GROUP BY
        e.id
ORDER BY 
        ts.id, e.startdate, e.id

Now my question is Can I use home_team and away_team in where clause. If yes then how. Like theoretically I want this in where clause.

WHERE home_team = 'Navibank Saigon' OR away_team = 'Navibank Saigon' ;

But it is giving Unknown Column error.

Please Help

Upvotes: 1

Views: 53

Answers (2)

Bart
Bart

Reputation: 10015

The reason why you can't use WHERE home_team = 'Navibank Saigon' OR away_team = 'Navibank Saigon' is that you assign these 'column names' (alias) on the result set after the search query is executed. The WHERE statement is part of the search query and needs the real column name, in this case p.name. On the other hand the ORDER BY statement is executed on the resultset and can thus use the column aliases like 'home_team'.

For the query part: you're using both teams in an aggregate function (MIN) on a GROUP BY statement. Filtering on a GROUP BY statement has to be done with HAVING instead of WHERE.

this results in:

GROUP BY
   e.id
HAVING
   MIN(IF( ep.number = 1, p.name, NULL)) = 'Navibank Saigon'
       OR MIN(IF( ep.number = 1, p.name, NULL)) = 'Navibank Saigon'

Upvotes: 0

Cody Caughlan
Cody Caughlan

Reputation: 32748

Looks like p.name is the true column name of what is being returned as home_team so make your WHERE clause like

WHERE p.name = 'Navibank Saigon'

Upvotes: 1

Related Questions