ambiguous outer join need help

J

Janis

I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
 
K

KARL DEWEY

I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;
 
J

Janis

Hi Karl:
I will try it tomorrow at work. I don't have a PC at home. I hope you can
check back tomorrow. I tried your suggestion on the self-join following the
supervisor/employee. I guess it is a recursive self-join. I'm having a bit
a trouble understanding the relationship. There is a nodeID--->1:M
nodeNameID. Then there is the parentNode. I guess that is why there is a
NodeName table? I'm not sure why I have them separate? when I was doing the
query it was harder since I have the 2nd node table and nodename table for
the self-join. In the employee/supervisor table there are only 2 tables
instead of 4.

I guess it is because there can be two nodes on one port? There can also be
one node on one port. It can be either a 1:1 or 1:M. That must be why I
have 4 tables instead of 2.
thanks,
Janis

KARL DEWEY said:
I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;

--
KARL DEWEY
Build a little - Test a little


Janis said:
I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
Click to expand...
Click to expand...
 
J

Janis

THANKS,

KARL DEWEY said:
I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;

--
KARL DEWEY
Build a little - Test a little


Janis said:
I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
Click to expand...
Click to expand...
 
J

Janis

Karl:
One thing I noticed is you changed everything to a left join.

I thought about the two tables for nodes,the ID table and the Name table. I
think the reason is because they have to segment nodes when the utilization
is over 50% on a port. So one of the nodes on one port can get moved which
would allow for that or if a node is split into two. I really don't know if
it could be combined into one table or not? I am going to test it with data
once I get the SQL query running.
Janis

KARL DEWEY said:
I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;

--
KARL DEWEY
Build a little - Test a little


Janis said:
I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
Click to expand...
Click to expand...
 
J

Janis

Do I have to change all my relationships in the access db to left joins or is
it just for this query?

KARL DEWEY said:
I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;

--
KARL DEWEY
Build a little - Test a little


Janis said:
I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not think so but I do not know your relationships that you have set.

As I said I still do not follow all that you are doing.
--
KARL DEWEY
Build a little - Test a little


Janis said:
Do I have to change all my relationships in the access db to left joins or is
it just for this query?

KARL DEWEY said:
I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;

--
KARL DEWEY
Build a little - Test a little


Janis said:
I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
Click to expand...
Click to expand...
Click to expand...
 
J

Janis

I think that the reason the node and nodeName tables are split is because
they might have to move one node to a new transmitter and also the parent
node can be in the first table. That way if they are split or move it will
probably be easier to tell what is going on?

I think that makes sense about the query relationships being left joins and
not the tables. In the query I want to see the router even if it doesn't
have any nodes. In that case it would be a mistake but I would want to see
it.

THANKS,

KARL DEWEY said:
I do not think so but I do not know your relationships that you have set.

As I said I still do not follow all that you are doing.
--
KARL DEWEY
Build a little - Test a little


Janis said:
Do I have to change all my relationships in the access db to left joins or is
it just for this query?

KARL DEWEY said:
I think you have too many joins. Try this ---
SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblNodeName_1.NodeName AS ParentNodeName, tblUtilization.[HSI_Utilization_%],
tblUtilization.date AS Weekending
FROM ((((((((tblArea LEFT JOIN tblHeadend ON tblArea.AreaID =
tblHeadend.AreaID) LEFT JOIN tblRouter ON tblHeadend.HeadEndID =
tblRouter.HeadEndID) LEFT JOIN tblPort ON tblRouter.RouterID =
tblPort.RouterID) LEFT JOIN tblTransmitter ON tblPort.PortID =
tblTransmitter.PortID) LEFT JOIN tblNode ON tblTransmitter.TransmitterID =
tblNode.TransmitterID) LEFT JOIN tblNodeName ON tblNode.NodeID =
tblNodeName.NodeID) LEFT JOIN tblNode AS tblNode_1 ON tblNode.NodeID =
tblNode_1.ParentNodeID) LEFT JOIN tblUtilization ON tblNode.NodeID =
tblUtilization.nodeID) LEFT JOIN tblNodeName AS tblNodeName_1 ON
tblNode_1.ParentNodeID = tblNodeName_1.NodeID
WHERE (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID;

--
KARL DEWEY
Build a little - Test a little


:

I need help on this query to fix the ambiguous outer join? I think I know
what the problem is but I don't know how to fix it. I have a self-join where
I want a parentNodeName showing when a node was split. The tableNode_1 is
the self-join. I added the tblNodeName_1 because there is more than one.
The problem could also be in the statistics table. How do I get the
utilization percentages on the parentNOde??

tia,


SELECT tblArea.AreaName, tblHeadend.Headend, tblNodeName.NodeName,
tblUtilization.[HSI_Utilization_%], tblUtilization.date AS Weekending,
tblNode_1.NodeID AS ParentNodeID, tblNodeName_1.NodeName AS ParentNodeName
FROM tblRouter AS tblRouter_1, (((tblArea INNER JOIN tblHeadend ON
tblArea.AreaID = tblHeadend.AreaID) INNER JOIN tblRouter ON
tblHeadend.HeadEndID = tblRouter.HeadEndID) INNER JOIN (tblPort INNER JOIN
tblTransmitter ON tblPort.PortID = tblTransmitter.PortID) ON
tblRouter.RouterID = tblPort.RouterID) INNER JOIN ((((tblNode LEFT JOIN
tblNode AS tblNode_1 ON tblNode.ParentNodeID = tblNode_1.NodeID) INNER JOIN
tblNodeName AS tblNodeName_1 ON tblNode_1.NodeID = tblNodeName_1.NodeID)
INNER JOIN tblNodeName ON tblNode.NodeID = tblNodeName.NodeID) INNER JOIN
tblUtilization ON tblNode.NodeID = tblUtilization.nodeID) ON
tblTransmitter.TransmitterID = tblNode.TransmitterID
GROUP BY tblArea.AreaName, tblHeadend.Headend
HAVING (((tblUtilization.date)=#8/3/2007#))
ORDER BY tblArea.AreaName, tblHeadend.Headend, tblRouter.RouterName,
tblPort.Port, tblTransmitter.Transmitter, tblNode.NodeID,
tblUtilization.date, tblNode_1.NodeID, tblNodeName_1.NodeName;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ambiguous outer join 6
Request for help with ambiguous outer join 1
ambiguous outer join - how to include query in an sql statement 0
Ambiguous outer join vs. multiple duplicative records 1

Top