Nicolas, select a.AnswerID,a.QuestionID,a.Value,a.AccountID,p.FirstName,p.LastName from Answer a left join Account c on (a.AccountID = c.AccountID), Person p where c.PersonID = p.PersonID
Best regards, Mikhail. ----- Original Message ----- From: "Nicolas Ivering" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 10:35 AM Subject: JOIN query with three tables. > Hi, this is probably a simple query but I tried all I can think of without finding a solution. > > I have three tables, > > CREATE TABLE Answer ( > AnswerID INTEGER NOT NULL AUTO_INCREMENT, > QuestionID INTEGER NOT NULL, > Value DECIMAL(15,4) NOT NULL, > AccountID INTEGER, > PRIMARY KEY (AnswerID) > ) TYPE=InnoDB; > > CREATE TABLE Account ( > AccountID INTEGER NOT NULL AUTO_INCREMENT, > PersonID INTEGER NOT NULL, > AccountGroupID INTEGER NOT NULL, > UserName VARCHAR(50) NOT NULL, > Password VARCHAR(20) NOT NULL, > PRIMARY KEY (AccountID) > ) TYPE=InnoDB; > > DROP TABLE Person; > > CREATE TABLE Person ( > PersonID INTEGER NOT NULL AUTO_INCREMENT, > FirstName VARCHAR(255) NOT NULL, > LastName VARCHAR(255) NOT NULL, > PRIMARY KEY (PersonID) > ) TYPE=InnoDB; > > Basicly, Answer can have an Account and an Account always has a Person. > > What would want is a query that produces a resultset where each row contains all fields from Answer + FirstName and LastName from Person for the AccountID that corresponds to the AccountID field in Answer. If an Answer does not have an AccountID I want NULL-values. > > Example: > > AnswerID | QuestionID | Value | AccountID | FirstName | LastName > 1 | 4 | 10.5 | 45 | John | Persson > 2 | 3 | 12.6 | NULL | NULL | NULL << FirstName and LastName is NULL because AccountID is NULL > > Oh, and I can not use version 4+. > > Help greatly appreciated. > > /Nicolas > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
