Thanks for your reply. Unfortunately that query will not select rows where Answer.AccountID is NULL which is what I wanted.
/Nicolas Mikhail Entaltsev wrote: >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 >> >> >> > > > -- Nicolas Serrador Ivering Greenmill Consulting AB Byängsgränd 20 SE-120 40 Årsta Sweden Office: +46 (0)8 664 7 664 Fax: +46 (0)8 664 7 864 Mobile: +46 (0)709 734237 Email: [EMAIL PROTECTED] --------------------------------------------------------------------- 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
