Unfortunately that wont always work either. For instance, assume that there is an A record with A_ID = 4 And that there is a C record where A_ID = 4, but NO B record where A_ID = 4
So, executing the query: > SELECT A_data, B_data, C_data > FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID > WHERE A.A_ID = 4; When A left joins B, there is no real B record, so any B columns are populated with null, as per left join. Then, table B is left joined to C on A_ID, which is null, and no C record will properly match the B.A_ID = NULL, so the C record is filled with nulls. If we were to join A to C then to B, a similar problem would occur if there was a cooresponding B record, but no C record. Thanks anyway. On Wednesday 01 October 2003 14:25, Kevin Fries wrote: > You're on the right track with LEFT JOIN. Just continue the thought... > Try: > SELECT A_data, B_data, C_data > FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID > WHERE A.A_ID = 4; > > > -----Original Message----- > > From: sean peters [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, October 01, 2003 12:07 PM > > To: [EMAIL PROTECTED] > > Subject: How to write this query > > > > > > I've run into a situation where i dont know how to best write > > a query. For a > > base example, consider these 3 tables: > > > > CREATE TABLE A ( > > A_ID INT NOT NULL PRIMARY KEY, > > A_data text > > ); > > > > CREATE TABLE B ( > > B_ID INT NOT NULL PRIMARY KEY, > > A_ID INT NOT NULL, > > B_data text > > ); > > > > CREATE TABLE C ( > > C_ID INT NOT NULL PRIMARY KEY, > > A_ID INT NOT NULL, > > C_data text > > ); > > > > So ive been running a query like: > > SELECT A_data, B_data, C_data FROM A, B, C > > WHERE A.A_ID = B.B_ID > > AND A.A_ID = C.C_ID > > AND A.A_ID = 4; > > > > What i really want is to get the A_data from A, and if there > > are cooresponding > > records in B and/or C, get B_data and/or C_data, respectively. > > > > This works fine if there are cooresponding records in tables > > B and C for each > > record in A, but if not, this returns nothing. > > > > So, short of querying each table, i cant come up with a good > > solution to my > > problem. > > > > If there were only 2 tables, a LEFT JOIN would work fine, but > > both B and C > > want to be left joined to A, which i dont know how to do. > > > > thanks > > sean peters > > [EMAIL PROTECTED] > > > > > > --- > > mysql, query > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
