Re: Updated: How to write this query

[Michael Brunson]

On Wed, 1 Oct 2003 16:58:26 -0500, sean peters
<[EMAIL PROTECTED]> wrote:
[...]
| So ive been running a query like:
| SELECT A_data, B_data, C_data FROM A, B, C
| WHERE A.A_ID = B.A_ID
| AND A.A_ID = C.A_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.


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;


That should do it.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Updated: How to write this query

[sean peters]

Sorry, I had an error in my query. The fixed query with the entire post 
follows. 

Thanks for the responses to the incorrect one, im pretty sure that the 
suggestions will still fail for the previously indicated reasons, even with 
the modified query.

ORIGINAL POST: (fixed)

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_IDINT NOT NULL PRIMARY KEY,
A_data  text
);

CREATE TABLE B (
B_IDINT NOT NULL PRIMARY KEY,
A_IDINT NOT NULL,
B_data  text
);

CREATE TABLE C (
C_IDINT NOT NULL PRIMARY KEY,
A_IDINT 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.A_ID
AND A.A_ID = C.A_ID
AND A.A_ID = 4;

*** ORIGINAL INCORRECT QUERY :( ***
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/[EMAIL PROTECTED]