Re: [sqlite] SQL Help (Joining and Grouping)
Robert Simpson wrote: > SELECT games.id, team1_id, team2_id, a.name AS team1, b.name AS team2, result > FROM games > INNER JOIN teams AS a ON team1_id = a.id > INNER JOIN teams AS b ON team2_id = b.id > WHERE a.name LIKE 'Suns' AND b.name LIKE 'Bulls' > > That'll find all games where the Suns played the Bulls and you can use that > as an example of how you'd query for other things as well. > Robert, excellent. That's exactly what I need. I didn't know that you could actually inner join the same table twice. That's quite powerfull. Thanke you Gilbert -- "The best portion of a good man's life is his little, nameless, unremembered acts of kindness and love." - Wordsworth, William
[sqlite] SQL Help (Joining and Grouping)
Hello, I send this once some days ago, but it didn't appear on the list, so i hope this time it'll work out. I know this is not directly related to SQLite, but hopefully someone has the time to help me with a (probably) basic SQL question: Consider the following tables: CREATE TABLE teams (id,name); CREATE TABLE games (id, date, team1_id, team2_id, result); team1_id and team2_id refer to the id in the teams table. 1. What query would be best suited to get an output so that the output would contain the teams names (not only the id) and the dates and results? 2. What query would be best if I want to search for games that two specific teams had played. For example, I want all the games that the teams with the names 'bla' and 'blub' played against each other. Is there any way to do that? I tried this with joins, with groupings, but wasn't able to get the lists i wanted. I can get the name of one team, but not the name of the other team. Any help would be appreciated Gilbert
[sqlite] SQL Question
Hello, I know this is not directly related to SQLite, but hopefully someone has the time to help me with a (probably) basic SQL question: Consider the following tables: CREATE TABLE teams (id,name); CREATE TABLE games (id, date, team1_id, team2_id, result); team1_id and team2_id refer to the id in the teams table. 1. What query would be best suited to get an output so that the output would contain the teams names (not only the id) and the dates and results? 2. What query would be best if I want to search for games that two specific teams had played. For example, I want all the games that the teams with the names 'bla' and 'blub' played against each other. Is there any way to do that? I tried this with joins, with groupings, but wasn't able to get the lists i wanted. I can get the name of one team, but not the name of the other team. Any help would be appreciated Gilbert
