CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2005
Dot NET
Dot NET

Reputation: 4897

How to display null if a field's where condition is not met

I've got the following SQL code which returns the tables in my database, along with the primary key field in each table.

SELECT Keys.TABLE_NAME As 'Table Name',
Keys.COLUMN_NAME AS 'Primary Key'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS Constraints
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS Keys
ON Constraints.TABLE_NAME = Keys.TABLE_NAME
AND Constraints.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
WHERE Constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'

As it is, it only displays those tables which have a primary key. How can I modify the sql to display all tables, and those tables which do not have a primary key will display "null" in the "Primary Key" column instead?

Upvotes: 0

Views: 582

Answers (2)

TimothyAWiseman
TimothyAWiseman

Reputation: 14863

By default a "join" is an inner join, which will only display a row if there is a corresponding row in the other table based on the join conditions. So, you will not want to use a straight join here, you need either a left join, right join, or full join which permit a table to return rows without a match.

But here you can't just switch to a left join because the table_constraints only lists ones that have constraints and also your where contraint will filter out rows that don't have a Primary Key.

So, you can bring in information_schema.tables to include every table and then move the primary key condition to the join conditions instead of the where clause. It could look something like:

SELECT t.TABLE_NAME As 'Table Name',  
Keys.COLUMN_NAME AS 'Primary Key'  
FROM 
INFORMATION_SCHEMA.TABLES as t
left join 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS Constraints  
on t.TABLE_NAME = constraints.TABLE_NAME and t.TABLE_SCHEMA = constraints.TABLE_SCHEMA
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS Keys  
ON Constraints.TABLE_NAME = Keys.TABLE_NAME  
AND Constraints.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME  
and Constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

To do this, you need to start with a list of all tables, and then join the tables using a left outer join:

SELECT t.TABLE_NAME As 'Table Name',
       Keys.COLUMN_NAME AS 'Primary Key'
FROM INFORMATION_SCHEMA.TABLES t left outer join
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS Constraints
     on t.TABLE_NAME = Constraints.Table_name and
        t.Table_Schema = Constraints.Table_Schema left outer join
     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS Keys 
     ON Constraints.TABLE_NAME = Keys.TABLE_NAME and
        Constraints.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME and
        Constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'

Upvotes: 1

Related Questions