LEFT JOIN problem

  • Thread starter Thread starter JohnH
  • Start date Start date
J

JohnH

Hello,

I am having problems trying to find a query that perfoms the following:

I have two tables defined as follows (excuse the SQL Server syntax)

CREATE TABLE [Rooms] (
[Room_ID] [Number],
[FirstTable] [Number],
[SecondTable] [Number],
)
CREATE TABLE [TableTypes] (
[Table_ID] [Number],
[Description] [Text],
)

Each room record contains a room id (key) and two table type id's. The
table type id's are not necessarily populated so thats where i think a
left join would be useful. What I want to do is display each row of the
rooms table with a table description instead of a table_ID.

The query i have at the moment is:

SELECT rooms.Roomid, rooms.firsttable, b.Description
FROM rooms LEFT JOIN tabletypes AS b ON rooms.FirstTable=b.table_ID;

But this doesn't show the SecondTable column description if you see what
I mean. I am using MS Access 2002 on XP.
Any help is much apreciated, thanks.
 
For reference i have solved this in SQL Server but i can't transfer the
syntax over to access. The LEFT JOIN syntax does not seem to work with
SQL Server. Here is the SQL Server solution:

select r.Room_ID, t.Description as 'First Table', b.Description as
'Second Table'
from rooms r, tableTypes t, tabletypes b
where (r.firsttable *= t.table_id) and (r.secondtable *= b.table_id)



Hello,

I am having problems trying to find a query that perfoms the following:

I have two tables defined as follows (excuse the SQL Server syntax)

CREATE TABLE [Rooms] (
[Room_ID] [Number],
[FirstTable] [Number],
[SecondTable] [Number],
)
CREATE TABLE [TableTypes] (
[Table_ID] [Number],
[Description] [Text],
)

Each room record contains a room id (key) and two table type id's. The
table type id's are not necessarily populated so thats where i think a
left join would be useful. What I want to do is display each row of the
rooms table with a table description instead of a table_ID.

The query i have at the moment is:

SELECT rooms.Roomid, rooms.firsttable, b.Description
FROM rooms LEFT JOIN tabletypes AS b ON rooms.FirstTable=b.table_ID;

But this doesn't show the SecondTable column description if you see what
I mean. I am using MS Access 2002 on XP.
Any help is much apreciated, thanks.
Click to expand...
 
Try in Access (JET Database Engine):

SELECT R.[Room_ID], T1.[Description], T2.[Description]
FROM
( Rooms AS R LEFT JOIN TableTypes AS T1
ON R.FirstTable = T1.Table_ID )
LEFT JOIN TableTypes AS T2
ON R.SEcondTable = T2.Table_ID

IN MS-SQL, the left join is "LEFT OUTER JOIN" but I am
sure "LEFT JOIN" should work fine also.

Using joins is probably more effcient than the WHERE
clause.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top