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 It won't work. Sorry. 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) left join Person p on (c.PersonID = p.PersonID) :) Best regards, Mikhail. ----- Original Message ----- From: "Mikhail Entaltsev" <[EMAIL PROTECTED]> To: "Nicolas Ivering" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, August 19, 2002 10:46 AM Subject: Re: JOIN query with three tables. > 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 > --------------------------------------------------------------------- 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
