Now I'm lost. Do you really mean to be joining A.A_ID against B.B_ID? Seems like it should be A.A_ID = B.A_ID... That's a traditional naming condition.
If so, my recommendation should have been: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID WHERE A.A_ID = 4; If not, maybe you can describe your data better, with examples. Regarding: > 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 That shouldn't be true. The join was (A left-join B), then that result set joined to C. And the comparison was A.A_ID = C.C_ID. If the resultset's A.A_ID has data, the C comparison will succeed regardless of B.B_ID being null. Kevin Fries > -----Original Message----- > From: sean peters [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 01, 2003 2:23 PM > To: Kevin Fries; [EMAIL PROTECTED] > Subject: Re: How to write this query > > > 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/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
