I am getting the following error when trying to use multiple joins in the second part of a nested CTE:
The multi-part identifier “ncpi.NodeID” could not be bound.
This is my code:
WITH ncp_ipbdn AS
(
SELECT
np.[NodeID],
np.City
FROM
[SWO_CP].[dbo].[NodesCustomProperties] np
INNER JOIN
(SELECT
npipb.NodeID,
nripb.[VALUE]
FROM
[SWO_CP].[dbo].[NodesCustomProperties] npipb,
[SWO_CP].[dbo].[NIP_Reference] nripb
INNER JOIN
[SWO_CP].[dbo].[NodesData] ndipb ON NodeID = ndipb.NodeID) ipb ON np.NodeID = ipb.NodeID
INNER JOIN
(SELECT
npipd.NodeID,
nripd.[VALUE]
FROM
[SWO_CP].[dbo].[NodesCustomProperties] npipd,
[SWO_CP].[dbo].[NIP_Reference] nripd
INNER JOIN
[SWO_CP].[dbo].[NodesData] ndipd ON NodeID = ndipd.NodeID) ipd ON np.NodeID = ipd.NodeID
INNER JOIN
(SELECT
npipn.NodeID,
nripn.[VALUE]
FROM
[SWO_CP].[dbo].[NodesCustomProperties] npipn,
[SWO_CP].[dbo].[NIP_Reference] nripn
INNER JOIN
[SWO_CP].[dbo].[NodesData] ndipn ON NodeID = ndipn.NodeID) ipn ON np.NodeID = ipb.NodeID
),
ncp_abmtdn AS
(
SELECT
ncpi.NodeID,
ncpi.City,
m.ObjectSubType AS [Method]
FROM
ncp_ipbdn ncpi,
[SWO_CP].[dbo].[NCP_Reference] a,
[SWO_CP].[dbo].[NCP_Reference] d
INNER JOIN
[SWO_CP].[dbo].[NodesData] m ON NodeID = m.NodeID
INNER JOIN
(SELECT
npt.NodeID,
nrt.[VALUE]
FROM
ncp_ipbdn npt,
[SWO_CP].[dbo].[NCP_Reference] nrt
INNER JOIN
[SWO_CP].[dbo].[NodesData] ndt ON NodeID = ndt.NodeID) t ON ncpi.NodeID = t.NodeID
)
SELECT *
FROM ncp_abmtdn;
If I only use a single join in the second CTE – it works, but once I added a second INNER JOIN to the second CTE it fails.
IMPORTANT: if I reduce the code to this line
INNER JOIN [SWO_CP].[dbo].[NodesData] m ON NodeID = m.NodeID
everything works fine. But once I add an additional JOIN after this line of code. I experience the aforementioned errors.
Use the below code to generate a sample:
CREATE TABLE [dbo].[NCP_Reference]
(
[TYPE] [nvarchar](400) NOT NULL,
[REFERENCE] [nvarchar](400) NULL,
[VALUE] [nvarchar](400) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[NodesCustomProperties]
(
[NodeID] [int] NOT NULL,
[City] [nvarchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[NodesData]
(
[NodeID] [nvarchar](max) NULL,
[Status] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [dbo].[NCP_Reference] (TYPE, REFERENCE, VALUE)
VALUES
('Test1','TST1','This is a Value1'),
('Test2','TST2','This is a Value2'),
('Test3','TST3','This is a Value3');
INSERT INTO [dbo].[NodesCustomProperties] (NodeID, City)
VALUES
('123','Miami'),
('223','Omaha');
INSERT INTO [dbo].[NodesData] (NodeID, Status)
VALUES
('123','TST1'),
('223','TST2'),
('223','TST3');
8
Looking at this subquery inside the first INNER JOIN in the first CTE:
SELECT npipb.NodeID,
nripb.[VALUE]
FROM [SWO_CP].[dbo].[NodesCustomProperties] npipb,
[SWO_CP].[dbo].[NIP_Reference] nripb
INNER JOIN [SWO_CP].[dbo].[NodesData] ndipb ON NodeID = ndipb.NodeID
Specifically, this line:
INNER JOIN [SWO_CP].[dbo].[NodesData] ndipb ON NodeID = ndipb.NodeID
The NodeID = ndipb.NodeID expression is ambiguous. Based on other clues in the query, there are at least three different columns that could possibly resolve to the first NodeID: np.NodeID, ndipb.NodeID, and npipb.NodeID. All of those tables are in scope at this point and are known to use this column name.
I think you probably wanted the expression to look like this:
ON npipb.NodeID = ndipb.NodeID
Other of the subqueries have similar structure with a similar issue.
And seriously: no more old-style “TableA,TableB” joins… They have been obsolete for more than 30 years! It’s especially bad when you don’t even have a WHERE clause, meaning you’re getting the cross product of the two tables. If that’s really what you want, CROSS JOIN is a thing. And for the love of God, whatever you do don’t mix the two styles.
2
This is not really an answer to the original problem, but are some general suggestions on restructuring and improving your query, eliminating the mixed old-style and new-style joins, and perhaps making the resulting query more readable, maintainable, and reliable.
Your table structure appears to consist of a parent-object having a many-to-many relationship with an properties table, using a junction table to support that many-to-many relationship. This is a fairly common table structure, and there are several techniques for looking up property values in these schemas.
For the purposes of this discussion, lets assume the following generic table structure:
Widget (WidgetId, WidgetName)WidgetProperty (WidgetId, PropertyId)Property (PropertyId, PropertyType, PropertyValue)
(Details like data types, primary keys, foreign keys, and indexes have been intentionally omitted. Just assume the standard stuff.)
In your original query, NodesCustomProperties is the parent-object (Widget), NodesData is the many-to-many junction table (WidgetProperty) and NCP_Reference is your property value table (Property).
Lets say each widget can have two properties of type PropertyType values of 1 and 2. (Normalization rules might demand a separate PropertyType lookup table, but let keep things simple.)
Selecting Multiple Separate Properties
We can look up the various properties by defining multiple joins to the WidgetProperty and Property tables, but with distinct aliases for each. The join conditions would also include a PropertyType selector to limit each branch of the join structure to just that property.
SELECT
W.WidgetId,
W.WidgetName,
P1.PropertyValue AS PropertyValue1,
P2.PropertyValue AS PropertyValue2
FROM Widget W
JOIN WidgetProperty WP1
ON WP1.WidgetId = W.WidgetId
JOIN Property P1
ON P1.PropertyId = WP1.PropertyId
AND P1.PropertyType = 1
JOIN WidgetProperty WP2
ON WP2.WidgetId = W.WidgetId
JOIN Property P2
ON P2.PropertyId = WP2.PropertyId
AND P2.PropertyType = 2
ORDER BY W.WidgetId;
This appears to be the equivalent of several of the nested queries in your originally-posted code.
Handling Missing Properties
It may be the case where not all properties are defined for all widgets. In this case, we can use a nested join – a combination of an inner and left-outer join.
LEFT JOIN (
WidgetProperty WP1
JOIN Property P1
ON P1.PropertyId = WP1.PropertyId
AND P1.PropertyType = 1
)
ON WP1.WidgetId = W.WidgetId
Note that the keyword sequence is JOIN...JOIN...ON...ON... instead of the usual JOIN...ON...JOIN...ON....
This causes the join between the two nested tables to be applied first before the combined results are joined (left-joined) to the rest of the query. This causes SQL Server to probe the joins two-deep through the WidgetProperty table to the Property, applying all conditions, before determining whether or not the outer LEFT JOIN has any matching results.
The syntax may take some getting used to, if you haven’t seen this nested join usage before. The parenthesis are optional, but are often included to improve readability – making it clear that the joins are being applied out-of-sequence.
Handling Duplicate Properties
Another problem that may occur is when a widget may have more than one property of the same type. If a widget had two properties with type = 1 and three properties with type = 2, the results would be the cartesian-product of these widgets, yielding six result rows. This is typically undesirable.
There are several ways to handle this, but we will focus on two here.
- Only select the best, latest, or top value for each property type.
- Combine multiple values into a comma-separated list or similar.
The following uses an OUTER APPLY (SELECT TOP 1 ... ORDER BY ...) pattern to select the best, latest, or top value for each property type.
OUTER APPLY (
SELECT TOP 1 P1.*
FROM WidgetProperty WP1
JOIN Property P1
ON P1.PropertyId = WP1.PropertyId
AND P1.PropertyType = 1
WHERE WP1.WidgetId = W.WidgetId
ORDER BY P1.PropertyDate DESC
) P1
The following will use the STRING_AGG() function to combine multiple values into a comma-separated list. The WITHIN GROUP specifies the order of values in the combined list. The operation is wrapped up in a CROSS APPLY.
CROSS APPLY (
SELECT STRING_AGG(P1.PropertyValue, ',')
WITHIN GROUP(ORDER BY P1.PropertyDate)
AS PropertyValueList
FROM WidgetProperty WP1
JOIN Property P1
ON P1.PropertyId = WP1.PropertyId
AND P1.PropertyType = 1
WHERE WP1.WidgetId = W.WidgetId
) P1
Summary
Each of the above code snippets can be repeated as many types as needed to cover all of the required attribute types. Only the aliases and PropertyType filter value needs to be changed from one case to the next. (Technically, the inner queries do not need unique table aliases. Whether or not to keep them distinct is a matter of preference.)
See this db<>fiddle for a demo of the various options from above.
See this db<>fiddle for an adaptation of the above to your original tables and data.
Thanks to @ThomA – I was able to find the answer.
I had a where clause at the end which was using ANSI-89 and this can’t be combined with ANSI-92. Once I wrapped that in () and converted the WHERE clause to a JOIN the query was stable.
1