1

I want to return column names into a table. But I also want to avoid redundancies like 'id' and 'id'.

I did:

INSERT INTO columns(name) 
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name='contacts' or table_name='companies';

But as expected I get id from the first table showing up with the same field name.

How do I get it in the form: contacts.id and companies.id?

2 Answers 2

1

You first try was very close. Just prepend the table_name and a dot:

INSERT INTO columns(name) 
SELECT CONCAT(TABLE_NAME,'.',COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name='contacts' or table_name='companies';

Give it a Try !!!

0

You can use GROUP BY:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'contacts' OR TABLE_NAME = 'companies' GROUP BY COLUMN_NAME;


INSERT INTO columns (name) SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='contacts' or TABLE_NAME='companies' GROUP BY COLUMN_NAME;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.