I tried to search in the forums but didn't find an answer to the following question. It's related to my old topic here, so I post it here instead of creating a new one.
Consider the following query (with the structure from the first post in this topic in mind).
SELECT homescore, awayscore
WHERE hometeamid = 1 OR awayteamid = 1
Now, I would only like the query to return the score for team id 1. Is it possible to make the SELECT statement dynamic, so it only returns a field depending on whether team id 1 is in the hometeamid or the awayteamid field?
So what I really want is something like SELECT homescore OR awayscore...
Of course this can be done in PHP afterwards, but it would be really nice if it's possible to do in the query.
I've tested and I think I need to have the condition in both queries, I haven't found anything else yet.
While I continue to test, I have another question. What if I want to JOIN a table2 (as described in the first post) and get the team names as well?
This doesn't work:
SELECT a.homescore, b.teamname, bb.teamname AS oppteam
FROM table1 a
JOIN table2 b ON a.hometeamid = b.teamid
JOIN table2 bb ON a.awayteamid = bb.teamid
WHERE hometeamid = 1 AND leagueid = 1
SELECT a.awayscore, b.teamname, bb.teamname AS oppteam
FROM table1 a
JOIN table2 b ON a.awayteamid = b.teamid
JOIN table2 bb ON a.hometeamid = bb.teamid
WHERE awayteamid = 1 AND leagueid = 1
ORDER BY date_played DESC