[RESOLVED] joining with information_schema table

[kebo]

what is going on?

something funny is going on I keep trying to post and end up with a new error page I've never seen, then I end up with a browser nested inside the window used to write the post.

I'm not simply being a wise guy here. I really have a question I need help with

[si_the_geek]

There is currently an odd issue with the forums... there are some things (and combinations of things) that cause that error message. One is the word System 32 (without a space), and others are so confusing we haven't worked out what is happening (eg: posting any 2 of 3 lines of a particular piece of code works, but all 3 together fails). The admins are looking into it, but we don't know when it will be fixed.

Try posting part of what you want, and edit in the other bits until it fails (you are likely to see a spinner not finishing at that point)... feel free to attach your question in a text file to save some effort.

[kebo]

Ok, thanks
Here is the post as an attachment.

[si_the_geek]

Testing to see if I can post the contents of your attachment...

Hey all,
I am trying to give my user a way to use "Nice Names" for one of my tables, and I need help with my select statement. I'm running mySQL a server instance.

I have 2 tables.

Table 1 has columns c1, c2, c3,..., cn and I have another table that looks like this...

fieldName | niceName
'c1' | 'column 1'
'c2' | 'column 2'
'c3' | 'column 3'
'c4' | 'column 4'

where the fieldName value is a column name from table1

I am able to select all of the columns from table 1 using this...

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='myDB' AND TABLE_NAME='table1'

but I don't know how to write the join to include any of the 'niceNames' that may exist in table2. I have tried the following (as well as other variations upon the theme), but I seem to be floundering from my lack of experience with joins.

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='myDB' AND TABLE_NAME='table1'
INNER JOIN `common_field_names_table`
ON `INFORMATION_SCHEMA`.`COLUMN_NAME` = `table2`.fieldName

Any help would be greatly appreciated.
thanks
kevin

edit: it worked, I guess the Quote tags etc helped.

[kebo]

yea, that's the post. I didn't try wrapping it in a quote.

[si_the_geek]

Move the WHERE clause to after the Joins (which are actually part of the From clause), and I suspect you want a Left join (as you implied the other table may not have matching rows)

SELECT COLUMN_NAME, `table2`.fieldName
FROM INFORMATION_SCHEMA.COLUMNS 
LEFT JOIN `common_field_names_table`
WHERE TABLE_SCHEMA='myDB' AND TABLE_NAME='table1'
ON `INFORMATION_SCHEMA`.`COLUMN_NAME` = `table2`.fieldName

[kebo]

I wish I understood sql a little better, because then I could probably figure this out. This is the SQL I am using, but there is still an issue with it.

SELECT `COLUMN_NAME`, `common_field_names_table`.`fieldName`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
LEFT JOIN `common_field_names_table`
WHERE `TABLE_SCHEMA`='myDB' AND `TABLE_NAME`='common_field_names_table'
ON `INFORMATION_SCHEMA`.`COLUMN_NAME` = `common_field_names_table`.`fieldName`

When I run it, I get the (always not very helpful) message back from the server....

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `TABLE_SCHEMA`='myDB' AND `TABLE_NAME`='common_field_names_table' ON `IN' at line 4

Can you see why the server doesn't like the syntax?

[kebo]

ok, after splashing around in the water, I finally have it...

SELECT c.COLUMN_NAME , t2.`niceName` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` As c
LEFT JOIN `table2` as t2
ON c.COLUMN_NAME  = t2.fieldName
WHERE c.`TABLE_SCHEMA`='mydb' AND c.`TABLE_NAME`='table1'

Every time I have to do this stuff, it make so much sense after the fact. Problem is I don't do it enough to make it stick and I have to figure out again each time.
Thanks si

[si_the_geek]

No worries, but I am disappointed in myself for not moving the ON line along with the rest of the Join