Hi,
This is regarding the task:
List of teams who played in the 2014 ‘Eighth-Final’ round:
I solved this query like this:
echo “$($PSQL “SELECT DISTINCT(name) FROM teams INNER JOIN games ON teams.team_id = games.winner_id OR teams.team_id = games.opponent_id WHERE round = ‘Eighth-Final’ AND year = 2014”)”
Notice the OR statement inside the ON statement. I don’t recall that it was taught in the studentDB module that you could join simultaneously on multiple columns inside the same table and much less the syntax to achieve it.
So if this is the correct way to solve this task (it ran, produced the correct result and I concluded the challenge successfully) I must say it seemed quite out of tune with the queries before and after which were much easier, and definitely had been taught.
The module being a beta version I think it is my duty to inform about this.
I was wracking my brains for probably an hour straight over this, and a lot of google searching brought up some other interesting things, but not this. A chance wording of a search took me to this post. Thanks so much for posting your solution. Do you remember how you came upon the usage of OR inside an ON statement? It didn’t come up in my searches and definitely wasn’t included in any of the lessons. I’d be curious as to what the expected method for solving this query was.
I agree with the original poster. I would be interested in knowing if anyone has used a different solution.
I thought something like this before checking your solution:
SELECT t1.name,t2.name FROM games FULL JOIN teams AS t1 ON games.winner_id = t1.team_id FULL JOIN teams AS t2 ON games.opponent_id = t2.team_id WHERE round='Eighth-Final' AND year=2014
It ‘works’, but it returns two columns because I’m selecting two things lol. I don’t know how to join them in a single column.
(I had to rename teams as t1 and t2 because if not, name becomes ambiguous)
this how I did it too. I didn’t know how to join them in a single column
Nice!
Been stuck on this query for about an hour lol. I don’t remember encountering this situation in the prep lessons. Nice to know we can use an OR there.
Only difference was I used a left join which gave correct answers as far as I can tell
Agreed. At first I breezed through most of the queries with little trouble, or just referencing the tutorials. Then I got to this one. As far as I can tell using the OR operator in the join was never taught, although using aliases to join the same table twice was covered.