CODEWITHSUNDEEP
sqljoinselectsyntax-errorssms
Mable
Mable

Reputation: 13

Simple SQL Server syntax assistance please

I'm banging my head against the wall with this one. Why does the query editor in SSMS give me an error about the closing parenthesis at the end of this query? 

SELECT 
    c.TABLE_NAME,
    c.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS c
INNER JOIN 
    (SELECT COLUMN_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
     GROUP BY COLUMN_NAME
     HAVING COUNT(*) = 1)

I get this error:

Incorrect syntax near ')'

Upvotes: 1

Views: 47

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You don't need to use join here, you can use exists :

select c.*
from information_schema.columns c
where not exists (select 1 
                  from information_schema.columns c1
                  where (c1.column_name = c.column_name) and
                        (c1.table_name <> c.table_name or c1.table_schema <> c.table_schema)
                );

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

You're missing an alias for the subquery in parenthesis and an on clause:

SELECT 
    c.TABLE_NAME,
    c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN 
    (SELECT 
        COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    GROUP BY COLUMN_NAME
    HAVING COUNT(*) = 1
    ) x -- Alias added here
    ON x.COLUMN_NAME = c.COLUMN_NAME -- ON caluse

Upvotes: 3

Related Questions