Help with nested left join - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with nested left join

Author

Topic

marxeil
Starting Member

4 Posts

Posted - 2009-01-02 : 09:12:41

I'm new here so hello everyone.

I have these 2 tables:
MESSAGE_ELEMENTS:
ELEMENT_PK
ELEMENT_NAME
MESSAGE_TYPE

SCHEMA_ELEMENTS:
SCHEMA
ELEMENT_PK (this is FK from ELEMENTS)
ELEMENT_SUPPORT_LEVEL

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_LEVEL
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 09:19:01

[code]
SELECT *
FROM MESSAGE_ELEMENTS me
LEFT JOIN SCHEMA_ELEMENTS se
ON se.ELEMENT_PK=me.ELEMENT_PK
AND se.SCHEMA=@YourSchema
[/code]

where @YourSchema is schema passed.

marxeil
Starting Member

4 Posts

Posted - 2009-01-02 : 09:27:56

This won't help for 2 reasons:
1. Its missing the message type as a parameter.
2. It will not return all the message elements. It will filter the results with the schema name (I tried this before).
It has to be a nested select because there are 2 steps required:
1. filter the SCHEMA_ELEMENTS according to the required schema.
2. Left join ELEMENTS with the result from step 1, while filtering ELEMENTS according to the required message type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 09:34:33

quote:
Originally posted by marxeil

This won't help for 2 reasons:
1. Its missing the message type as a parameter.
2. It will not return all the message elements. It will filter the results with the schema name (I tried this before).
It has to be a nested select because there are 2 steps required:
1. filter the SCHEMA_ELEMENTS according to the required schema.
2. Left join ELEMENTS with the result from step 1, while filtering ELEMENTS according to the required message type.

just change like this


SELECT *
FROM MESSAGE_ELEMENTS me
LEFT JOIN SCHEMA_ELEMENTS se
ON se.ELEMENT_PK=me.ELEMENT_PK
AND se.SCHEMA=@YourSchema
WHERE me.MESSAGE_TYPE=@messgae_type

what you asid about 2 is not true.it will return all message elements
and return those associated schema details where schema name is passed on value

marxeil
Starting Member

4 Posts

Posted - 2009-01-02 : 10:15:08

Sorry, it was my mistake.
I just noticed that this is SQL server 2005 dedicated forum. I am trying to do this with MS ACCESS.
I tried your code in SQL SERVER 2005 and it works perfectly. Access however does not support this syntax.
So sorry again and I'll take myself and my question to the right forum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 10:20:50

quote:
Originally posted by marxeil

Sorry, it was my mistake.
I just noticed that this is SQL server 2005 dedicated forum. I am trying to do this with MS ACCESS.
I tried your code in SQL SERVER 2005 and it works perfectly. Access however does not support this syntax.
So sorry again and I'll take myself and my question to the right forum.

No problem

Unforunately i'm not an Access expert, so please post this in Access forum here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources