Help with a SELECT statement

I have 4 tables. The first table contains details for a customer order. The second contains inventory items. The third contains alternate names for these items specific to the customer. The fourth holds the relationship between the items and the alternate name specific to the customer. Now with one SELECT statement I want to be able to return two columns. The first column will contain the Identity field from table two. The second column will contain the name field from table two unless there are matching records in table three as determined in the relationship established in table four. I the case there are matching records in table three then column two should contain the contents of the name field from table three. I now have a SELECT statement with as series of INNER and LEFT OUTER JOINS that returns three columns. The first is the identity fields. The second is the name field from table two. The third is the name field from table three. If I could somehow get the third column into column two where column three isn’t NULL and return this dataset I’d have my problem solved. All of this done with one SELECT statement. If I can’t do this in one SELECT statement I’ll have to do with code behind (not how I would prefer to do this).

SELECT DISTINCT
StandardItem.[Id], StandardItem.[Name], AltItem.[Name] AS AltName
FROM StandardItem
INNER JOIN Orders ON StandardItem.[Id] = ItemId
AND Orders.payorID = 3
LEFT OUTER JOIN ItemMap ON ItemId = StandardItem.[Id]
LEFT OUTER JOIN AltItem ON AltItem.[Id] = ItemMap.AltItemId


Any suggestions will be greatly appreciated

Thank you,
iRead

This only my 10 cents worth but i am sure that you need to put all the tables you wish to query from in the FROM part of the select statement.

So you would have

SELECT DISTINCT
StandardItem.[Id], StandardItem.[Name], AltItem.[Name] AS AltName
FROM StandardItem, Orders, ItemMap, AltItem
INNER JOIN Orders ON StandardItem.[Id] = ItemId
AND Orders.payorID = 3
LEFT OUTER JOIN ItemMap ON ItemId = StandardItem.[Id]
LEFT OUTER JOIN AltItem ON AltItem.[Id] = ItemMap.AltItemId

But i could be wrong.

Oli.

oli, sorry, yes, you are

iread, i've read your question a couple of times and just don't get it

you may have to show the relationships by displaying a few rows from each table

I came up with this:

SELECT DISTINCT
StandardItem.[Id], StandardItem.[Name]
FROM StandardItem
INNER JOIN Orders ON StandardItem.[Id] = ItemId
AND Orders.payorID = 3
LEFT OUTER JOIN ItemMap ON ItemId = StandardItem.[Id]
LEFT OUTER JOIN AltItem ON AltItem.[Id] = ItemMap.AltItemId\
WHERE AltItem.[Name] IS NULL
UNION
SELECT DISTINCT
StandardItem.[Id], AltItem.[Name]
FROM StandardItem
INNER JOIN Orders ON StandardItem.[Id] = ItemId
AND Orders.payorID = 3
LEFT OUTER JOIN ItemMap ON ItemId = StandardItem.[Id]
LEFT OUTER JOIN AltItem ON AltItem.[Id] = ItemMap.AltItemId\
WHERE AltItem.[Name] IS NOT NULL

Works brilliantly! Thank you for your kind input.

iRead