Very Computer

Is it possible in T-SQL to get a value from across a
one-to-many-and-many-to-one relationship?

Let me explain. I've got three tables: STORY, LINKS and FOLDERS. A record in
STORY is linked in a one-to-many relationshipo to LINKS with its STORY_ID.
LINKS is linked to FOLDER in a many-to-one relationship. Like so:

STORY
    storyID
    storyName

LINKS
    storyID
    folderID

FOLDER
    folderID
    folderName

I'd like to link storyNAMEs to folderNames. But when I do what seems to be
correct, I get an error about an "illegal outer join request."

Here's the SQL I'm using:

select STORY.storyName, FOLDER.folderName from STORY, FOLDER where
STORY.storyID*=LINKS.storyID and LINKS.folderID*=FOLDER.folderName

I've futzed around with a few variations, but none that bring success.

I'm able to do this in Microsoft Access (it does an outer join on an outer
join). There must be a way to do it in SQL Server.

Is there?

Try following:

select STORY.storyName,   FOLDER.folderName
from
        STORY, FOLDER , LINKS
where
        LINKS.storyID*=STORY.storyID and
        LINKS.folderID*=FOLDER.folderID
go

Manu

Quote:

> Here's the SQL I'm using:

> select STORY.storyName, FOLDER.folderName from STORY, FOLDER where
> STORY.storyID*=LINKS.storyID and LINKS.folderID*=FOLDER.folderName

-am

You're right that the LINKS table needs to be included. That's my typo and
my apologies.

Using a plain join does allow the links, but if a STORY has no related LINK,
it won't appear, will it? That's how I got into the outer join thing. Unless
I'm mistaken.

I did examine how MS Access does this and learned that it creates a sort of
pseudo-table that's the "from" table. It's outer-joined between STORY and
LINKS, then that whole thing, as a single entity is outer-joined on FOLDER.
Like so:

SELECT STORY.SLUG, FOLDER.FOLDER
 FROM (LINKS RIGHT JOIN STORY ON LINKS.STORY_ID = STORY.STORY_ID)
LEFT JOIN FOLDER ON LINKS.FOLDER_ID = FOLDER.FOLDER_ID

"Right inner join" and "left inner join" is their syntax for "=*" and "*=".
I wonder if Sybase has something similar.

Thanks. I tried, but that gives me:

"Query contains an illegal outer-join request"

Quote:>Try following:

>select STORY.storyName,   FOLDER.folderName
>from
>        STORY, FOLDER , LINKS
>where
>        LINKS.storyID*=STORY.storyID and
>        LINKS.folderID*=FOLDER.folderID
>go

>Manu

>> Here's the SQL I'm using:

>> select STORY.storyName, FOLDER.folderName from STORY, FOLDER where
>> STORY.storyID*=LINKS.storyID and LINKS.folderID*=FOLDER.folderName

Quote:> I did examine how MS Access does this and learned that it creates a sort of
> pseudo-table that's the "from" table. It's outer-joined between STORY and
> LINKS, then that whole thing, as a single entity is outer-joined on FOLDER.
> Like so:

> SELECT STORY.SLUG, FOLDER.FOLDER
>  FROM (LINKS RIGHT JOIN STORY ON LINKS.STORY_ID = STORY.STORY_ID)
> LEFT JOIN FOLDER ON LINKS.FOLDER_ID = FOLDER.FOLDER_ID

> "Right inner join" and "left inner join" is their syntax for "=*" and "*=".

Quote:> I wonder if Sybase has something similar.

-am

Thanks. I think that's what the Access code is doing. It's just insulating
the user from it a bit.

select STORY.storyName, FOLDER.folderName
from STORY, FOLDER, LINKS
where STORY.storyID = LINKS.storyID
and LINKS.folderID = FOLDER.folderID

--

Best regards,
Alex Ratner