CODEWITHSUNDEEP
mysqlsql
Donald Hwong
Donald Hwong

Reputation: 13

Please help to identify my error in mysql query

The following query works, but return the primary key multiple times, while there was only one primary key. Please help me to find the error. Thank you.

SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_CATALOG =    
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_CATALOG
   AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA = 
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA
   AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = 
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME
RIGHT OUTER JOIN INFORMATION_SCHEMA.COLUMNS ON 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME 
   AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
   AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG = 
       INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG
WHERE (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '#attributes.table#')
ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION```

Upvotes: 1

Views: 71

Answers (1)

Md. Mahmud Hasan
Md. Mahmud Hasan

Reputation: 1063

Add one more condition in first join 

AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME = 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME

Distinct will also work.

Upvotes: 2

Related Questions