Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Help with nested left join

Author  Topic  marxeil Starting Member 4 Posts Posted - 2009-01-02 : 10:19:16 I am having problems with this query: I have these 2 tables: MESSAGE_ELEMENTS: ELEMENT_PK ELEMENT_NAME MESSAGE_TYPE SCHEMA_ELEMENTS: SCHEMA ELEMENT_PK (this is FK from ELEMENTS) SUPPORT The first table stores a list of elements in message types (each message type is made of a list of elements). The second table stores which fields in a message type are valid in a particular schema. What I want is to get a list of elements for a message type and display the support details for a particular schema: I tried something like this: SELECT MESSAGE_ELEMENTS.* FROM MESSAGE_ELEMENTS LEFT JOIN (SELECT SCHEMA_ELEMENTS.*, RESULT.SUPPORT FROM SCHEMA_ELEMENTS WHERE SCHEMA_ELEMENTS.SCHEMA_NAME = [schema name] AS RESULT) ON MESSAGE_ELEMENTS.ELEMENT_PK = RESULT.ELEMENT_PK WHERE MESSAGE_ELEMENTS.MESSAGE_TYPE = [message type] I tried all kind of variations on this and always get errors or wrong results. What am I doing wrong? Thanks