Join error in query

[lorirobn]

Hello,

I would greatly appreciate if someone could tell me what is wrong with this query. I am getting a JOIN error. It was working fine, then I added another lookup table to it.

The problematic sql is:

SELECT tblRoomItems.RoomID AS Room,
tblSpaceUse.RoomTypeID AS [Room Type], 
tblSpaceUse.RoomLocation AS Location, 
tblSpaceUse.HousingGuestracClassif AS [Guestrac Code], 
tblItems.ItemDesc AS Showerhead,
 tlkpCondition.ConditionDesc AS Cond
FROM tblSpaceUse 
INNER JOIN (tlkpItemSubcategory
INNER JOIN (tblItems 
INNER JOIN (tblRoomItems 
INNER JOIN (tlkpRoomCategory
INNER JOIN tlkpCondition ON tblRoomItems.ConditionID = tlkpCondition.ConditionID) 
ON tblSpaceUse.RoomCategoryID = tlkpRoomCategory.RoomCategoryID)
ON tblItems.ItemID = tblRoomItems.ItemID)
ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID) 
ON tblSpaceUse.RoomID = tblRoomItems.RoomID
WHERE (((tlkpItemSubcategory.ItemSubcatID)='shrhd'));

What have I done wrong? Parentheses? Fields out of order (btw, do they need to be in any particular order on the joins?).

This is the query that works fine, before my attempt to add another table. The table I added is tlkpRoomCategory.

SELECT tblRoomItems.RoomID AS Room, tblSpaceUse.RoomCategoryID, 
tblSpaceUse.RoomTypeID AS [Room Type], tblSpaceUse.RoomLocation AS Location, tblSpaceUse.HousingGuestracClassif AS [Guestrac Code], tblItems.ItemDesc AS Showerhead, tlkpCondition.ConditionDesc AS Cond
FROM tblSpaceUse 
INNER JOIN (tlkpItemSubcategory 
INNER JOIN (tblItems 
INNER JOIN (tblRoomItems 
INNER JOIN tlkpCondition 
ON tblRoomItems.ConditionID = tlkpCondition.ConditionID) ON tblItems.ItemID = tblRoomItems.ItemID) 
ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID) 
ON tblSpaceUse.RoomID = tblRoomItems.RoomID
WHERE (((tlkpItemSubcategory.ItemSubcatID)='shrhd'));

I have been struggling and struggling and changing it, all to no avail. Any pointers?

Many thanks in advance...
Lori

 

[Golom]

You are doing this

(tlkpRoomCategory INNER JOIN tlkpCondition 
 ON tblRoomItems.ConditionID = tlkpCondition.ConditionID)

but your ON condition specifies a table (tblRoomItems) that is outside the parentheses and does not mention one of the tables (tlkpRoomCategory) that is inside them.


[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lorirobn]

uh-oh, I think I need a quick course on how to construct a join statement. The tlkpRoomCategory is supposed to join tblSpaceUse with tlkpRoomCategory. It uses tblSpaceUse.RoomCategoryID to do this join, to tlkpRoomCategory.RoomCategoryID.
When I added the one additional field, I did not know where to place it.
(btw, I deleted all the parentheses from my working example inadvertently).

 

[lespaul]

check out Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

Assuming that the Cut & Paste Gods are cooperating ... try this

FROM ((((tblSpaceUse 
INNER JOIN tblRoomItems 
ON tblSpaceUse.RoomID = tblRoomItems.RoomID)

INNER JOIN tlkpCondition 
ON tblRoomItems.ConditionID = tlkpCondition.ConditionID)

INNER JOIN tlkpRoomCategory 
ON tblSpaceUse.RoomCategoryID = tlkpRoomCategory.RoomCategoryID)

INNER JOIN tblRoomItems 
ON tblItems.ItemID = tblRoomItems.ItemID)

INNER JOIN tlkpItemSubcategory 
ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

No ... they weren't

FROM ((((tblSpaceUse 
INNER JOIN tblRoomItems 
ON tblSpaceUse.RoomID = tblRoomItems.RoomID)

INNER JOIN tlkpCondition 
ON tblRoomItems.ConditionID = tlkpCondition.ConditionID)

INNER JOIN tlkpRoomCategory 
ON tblSpaceUse.RoomCategoryID = tlkpRoomCategory.RoomCategoryID)

INNER JOIN tblItems 
ON tblItems.ItemID = tblRoomItems.ItemID)

INNER JOIN tlkpItemSubcategory 
ON tlkpItemSubcategory.ItemSubcatID = tblItems.ItemSubcategoryID

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lorirobn]

WOW - IT WORKED!!!!!! I can't believe it - I have been struggling so long with this one. Thank you so much, Golom, for writing back and showing me how to correctly write the SQL. I didn't realize you could put the INNER JOIN and the ON together - it's sooo much easier to follow! I had them broken apart only because Access created the original SQL that way, and I was just modifying it.

I did take a look at the web sites mentioned above, but I didn't find any statements having multiple joins like mine. While I understand the concept of joins, I hadn't known the correct syntax for complicated statements.

Thank you!!

a now-happy sql coder...
Lori