LEFT JOIN Multiple Tables

[jim]

Hi,

I've browsed several posts, but still haven't found the answer I'm
looking for. I have one table (A) that contains a list of values I
want to return. I have two other tables (B) and (C) that may or may
not include the same values, but will have others I need to include.
I'd like to return all of the data from table A with a yes/no column
if table B has matching data and a third column of the same for table
C.

I can get one or the other, but I've not figured out how to do
multiple LEFT JOINs in Access. Any help would be greatly
appreciated. Here's a version of my query:

SELECT A.id
, A.date
, SWITCH(B.id <null, "0"
,B.id IS null,"1") AS B
, SWITCH(C.id <null, "0"
,C.id IS null,"1") AS C

FROM A

LEFT JOIN B
ON A.id = B.id

LEFT JOIN C
ON A.id = C.id
Thanks in advance to anyone with pointers.
Jim
;

[Allen Browne]

Jim, I'm a little confused, since you seem to be using B and C as table
names and also as field aliases.

You are also comparing a field to Null, which won't work. Need to test if it
Is Null.

Is this what you want:

SELECT A.id,
A.date,
(B.id Is Null) AS BidNull,
(C.id Is Null) AS CidNull
FROM (A LEFT JOIN B ON A.id = B.id)
LEFT JOIN C ON A.id = C.id;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim" <ji*********@gmail.comwrote in message
news:11*********************@u32g2000prd.googlegro ups.com...

>
I've browsed several posts, but still haven't found the answer I'm
looking for. I have one table (A) that contains a list of values I
want to return. I have two other tables (B) and (C) that may or may
not include the same values, but will have others I need to include.
I'd like to return all of the data from table A with a yes/no column
if table B has matching data and a third column of the same for table
C.

I can get one or the other, but I've not figured out how to do
multiple LEFT JOINs in Access. Any help would be greatly
appreciated. Here's a version of my query:

SELECT A.id
, A.date
, SWITCH(B.id <null, "0"
,B.id IS null,"1") AS B
, SWITCH(C.id <null, "0"
,C.id IS null,"1") AS C

FROM A

LEFT JOIN B
ON A.id = B.id

LEFT JOIN C
ON A.id = C.id

[Tom van Stiphout]

On 24 Apr 2007 11:56:35 -0700, jim <ji*********@gmail.comwrote:

Rather than "<null" use "is not null".

-Tom.

>Hi,

I've browsed several posts, but still haven't found the answer I'm
looking for. I have one table (A) that contains a list of values I
want to return. I have two other tables (B) and (C) that may or may
not include the same values, but will have others I need to include.
I'd like to return all of the data from table A with a yes/no column
if table B has matching data and a third column of the same for table
C.

I can get one or the other, but I've not figured out how to do
multiple LEFT JOINs in Access. Any help would be greatly
appreciated. Here's a version of my query:

SELECT A.id
, A.date
, SWITCH(B.id <null, "0"
,B.id IS null,"1") AS B
, SWITCH(C.id <null, "0"
,C.id IS null,"1") AS C

FROM A

LEFT JOIN B
ON A.id = B.id

LEFT JOIN C
ON A.id = C.id
Thanks in advance to anyone with pointers.
Jim
;

[jim]

Thanks all. Let me add a little more detail.

I have a table with a list of ids (A). Another table (B), contains a
subset of those ids and table (C) a different subset. I ultimately
want to return the entire contents of table A with a fag in the
second column when the id matches with table B and a flag in the
third column when it matches with table C. When I LEFT JOIN either of
the tables as it's set up everything works just fine. When I try to
join *both* tables, however, the query fails to execute.

The alias is just a poor job of translating actual table names here
for illustrative purposes. The meaning should be the same.

[Larry Linson]

For a tables tblA, with an AutoNumber primary key of id, and tblB, and tblC,
each with a LongInteger key of id, the following seems to work as you
describe... instead of calculating a positive 1 for the flag, I used a
True/False, which generated a -1 for the flag to indicate existence of the
record.

[Larry Linson]

For a tables tblA, with an AutoNumber primary key of id, and tblB, and tblC,
each with a LongInteger key of id, the following seems to work as you
describe... instead of calculating a positive 1 for the flag, I used a
True/False, which generated a -1 for the flag to indicate existence of the
record. Here's the SQL for the Query, which I created in the Query Builder
and just copied SQL view:

SELECT tblA.id, IsNull([tblB].[id]) AS flgB, IsNull([tblC].[Id]) AS flgC
FROM (tblA LEFT JOIN tblB ON tblA.id = tblB.id) LEFT JOIN tblC ON tblA.id =
tblC.id;

Larry Linson
Microsoft Access MVP


"jim" <ji*********@gmail.comwrote in message
news:11*********************@u32g2000prd.googlegro ups.com...

Hi,

I've browsed several posts, but still haven't found the answer I'm
looking for. I have one table (A) that contains a list of values I
want to return. I have two other tables (B) and (C) that may or may
not include the same values, but will have others I need to include.
I'd like to return all of the data from table A with a yes/no column
if table B has matching data and a third column of the same for table
C.

I can get one or the other, but I've not figured out how to do
multiple LEFT JOINs in Access. Any help would be greatly
appreciated. Here's a version of my query:

SELECT A.id
, A.date
, SWITCH(B.id <null, "0"
,B.id IS null,"1") AS B
, SWITCH(C.id <null, "0"
,C.id IS null,"1") AS C

FROM A

LEFT JOIN B
ON A.id = B.id

LEFT JOIN C
ON A.id = C.id
Thanks in advance to anyone with pointers.
Jim
;