Sean, Slight rewriting of Kevin's query--I assume you want to do the joins on A_ID.
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; This should work. For your example, the first left join gives a table with A.* and nulls for B.*. Then, the second left join gives you C.* for that A_ID; it doesn't matter that the B.* part contains nulls. Bill > From: sean peters <[EMAIL PROTECTED]> > To: "Kevin Fries" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> > Subject: Re: How to write this query > Date: Wed, 1 Oct 2003 16:22:46 -0500 > 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]
