Incorrect column qualifier adding in DELETE statement with multiple JOINs

PhilipD

SSMS 19.1.56.0 on Windows 10

SQL Prompt 10.13.16.3650

Connecting to SQL Server 2019 on a different machine

SQL Prompt does not seem to correctly qualify some column names in a DELETE query with several JOIN clauses.

I have a small stored procedure like the following, with a couple of issues reported by SQL Prompt in the script for "Schema name for table or view is not specified" on "MAINTABLE" in the first line and "Unqualified column name" on "ParentKeyID" and "PersonID" in the last line:

DELETE FROM MAINTABLE

WHERE PersonID NOT IN (SELECT CCCC.PersonID 

FROM DB2.dbo.TABLE-A AAAA

INNER JOIN DB2.dbo.TABLE-B BBBB ON AAAA.AAAA-ID = BBBB.AAAA-ID AND BBBB.BBBB-ID = (SELECT MAX(BBBB-ID) FROM DB2.dbo.TABLE-B WHERE AAAA-ID = AAAA.AAAA-ID AND Category IN ('Active','Draft'))

INNER JOIN DB2.dbo.TABLE-C CCCC ON BBBB.BBBB-ID = CCCC.BBBB-ID 

WHERE AAAA.AAAA-ID = ParentKeyID AND CCCC.PersonID = PersonID);

If I select "MAINTABLE" in the first line above and click "Qualify object name" on the lightbulb icon, the following changes are made - "MAINTABLE" is qualified correctly, but "PersonID" is incorrectly qualified with the wrong table name, and the "ParentKeyID" column is not qualified at all:

DELETE FROM dbo.MAINTABLE

WHERE PersonID NOT IN (SELECT CCCC.PersonID 

FROM DB2.dbo.TABLE-A AAAA

INNER JOIN DB2.dbo.TABLE-B BBBB ON AAAA.AAAA-ID = BBBB.AAAA-ID AND BBBB.BBBB-ID = (SELECT MAX(BBBB-ID) FROM DB2.dbo.TABLE-B WHERE AAAA-ID = AAAA.AAAA-ID AND Category IN ('Active','Draft'))

INNER JOIN DB2.dbo.TABLE-C CCCC ON BBBB.BBBB-ID = CCCC.BBBB-ID 

WHERE AAAA.AAAA-ID = ParentKeyID AND CCCC.PersonID = CCCC.PersonID);

I ended up manually adjusting the column qualifications after adding an alias to the first table in the DELETE clause, as follows:

DELETE MAINALIAS FROM dbo.MAINTABLE AS MAINALIAS

WHERE PersonID NOT IN (SELECT CCCC.PersonID 

FROM DB2.dbo.TABLE-A AAAA

INNER JOIN DB2.dbo.TABLE-B BBBB ON AAAA.AAAA-ID = BBBB.AAAA-ID AND BBBB.BBBB-ID = (SELECT MAX(BBBB-ID) FROM DB2.dbo.TABLE-B WHERE AAAA-ID = AAAA.AAAA-ID AND Category IN ('Active','Draft'))

INNER JOIN DB2.dbo.TABLE-C CCCC ON BBBB.BBBB-ID = CCCC.BBBB-ID 

WHERE AAAA.AAAA-ID = MAINALIAS.ParentKeyID AND CCCC.PersonID = MAINALIAS.PersonID);

lgivens

Hi Philip,
 
Let me see if I can answer your questions about SQL Prompt. I don't know all of your table structure, but based on what is visible in your query, I tried to duplicate what you are seeing in my test environment. As you saw Prompt recommended you add the schema to the table name. This is a best practice rule. This rule can be turned on/off via SQL Prompt > Code Analysis Rules, scroll down to Performance Rules and uncheck PE002 Schema name for table or view is not specified.
 
The two column names on the last line are a little more problematic, because I don't have all the information. But I think the best practice rule is to always include table name or alias with column names when using a join clause. SQL Prompt couldn't 'fix' the column names because it didn't know what table to look in (this is assuming that Table-A doesn't have ParentKeyID or PersonID columns).
 
So the best practice way to write the query, according to SQL Prompt, would be:
 
SELECT * FROM dbo.Maintable mt
WHERE mt.PersonID NOT IN (SELECT CCCC.PersonID FROM dbo.TABLEA AAAA
INNER JOIN dbo.TABLEB BBBB ON AAAA.AAAA-ID = BBBB.AAAA-ID AND BBBB.BBBB-ID = (SELECT MAX(BBBB-ID) FROM dbo.TABLEB WHERE AAAA-ID = AAAA.AAAA-ID AND Category IN ('Active','Draft'))
INNER JOIN dbo.TABLEC CCCC ON BBBB.BBBB-ID = CCCC.BBBB-ID
WHERE AAAA.AAAA-ID = mt.ParentKeyID AND CCCC.PersonID = mt.PersonID);
 
With that syntax, SQL Prompt wasn't highlighting anything other than to recommend using EXISTS other than IN for the main WHERE clause.
 
Does this help?
 

Thank you,

Lee Givens
Product Support Engineer