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 ---------------------------------------- Return-Path: <[EMAIL PROTECTED]> Fri Feb 25 11:18:00 2005 Received: from sqlite.org [67.18.92.124] by mail.bcsft.com with SMTP; Fri, 25 Feb 2005 11:18:00 -0700 Received: (qmail 32637 invoked by uid 1000); 25 Feb 2005 18:10:53 -0000 Received: (qmail 32630 invoked from network); 25 Feb 2005 18:10:52 -0000 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm Precedence: bulk List-Id: SQLite Users Mailing List <sqlite-users.sqlite.org> List-Post: <mailto:[email protected]> List-Help: <mailto:[EMAIL PROTECTED]> List-Unsubscribe: <mailto:[EMAIL PROTECTED]> List-Subscribe: <mailto:[EMAIL PROTECTED]> Reply-To: [email protected] Delivered-To: mailing list [email protected] Message-ID: <[EMAIL PROTECTED]> Date: Fri, 25 Feb 2005 19:10:17 +0100 From: Gilbert Jeiziner <[EMAIL PROTECTED]> Reply-To: [email protected] User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.5) Gecko/20041215 Thunderbird/1.0 Mnenhy/0.6.0.103 X-Accept-Language: en-us, en MIME-Version: 1.0 To: [email protected] X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Subject: [sqlite] SQL Help (Joining and Grouping) X-SmarterMail-Spam: SPF_Pass X-Rcpt-To: <[EMAIL PROTECTED]> 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
