complicated Join - duplicate row problem

Hi
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))
and then (assuming table2 exists)
ALTER TABLE table1
ADD CONSTRAINT FK_table1_table2
FOREIGN KEY (col1)
REFERENCES table2 (col1)
REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK | FK
col2 | varchar | PK |
col3 | integer | |
My problem is getting the PK and FK on the same row. My results are
currently like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK |
col1 | varchar | | FK
col2 | varchar | PK |
col3 | integer | |
HERE IS THE QUERY:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END,
FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.
Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?
Thanks
JeffHi, Jeff
Did you read my response to your previous post ?
http://groups.google.com/group/micr...br />
a2bf3ce9
It's exactly what you are requesting now...
Razvan|||Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.
This looks good and makes sense.
Thank you very very much !!
Jeff
On 12 Jan 2006 23:28:37 -0800, "Razvan Socol" <rsocol@.gmail.com>
wrote:

>Hi, Jeff
>Did you read my response to your previous post ?
>http://groups.google.com/group/micr...r />
9a2bf3ce9
>It's exactly what you are requesting now...
>Razvan|||On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:
(snip)
>Therefore, I would like my result records to look like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK | FK
> col2 | varchar | PK |
> col3 | integer | |
>My problem is getting the PK and FK on the same row. My results are
>currently like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK |
> col1 | varchar | | FK
> col2 | varchar | PK |
> col3 | integer | |
(snip)
Hi Jeff,
Just a few simple changes to the query should suffice:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END),
FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION
Hugo Kornelis, SQL Server MVP