I have this query that selects the tables like CODE_. I want to join these results to each CODE table like CODE_COUNTRY, CODE_COUNTY to list the value of the SHORT_DESC, it will be values like: United States, Mexico for Country; Brown, Green for County; Male, Female For Gender*. The value in the SHORT_DESC matches the value in the Transformations Table PC_Column*. So I need to join to the Transformations table to find the columns that match. And do left join, right joins to show columns that don't match either database. How do I find the system column to join to the Transformations table?
SELECT tb.[schema_id] AS 'Schema'
,tb.[OBJECT_ID] AS 'TableObj'
,tb.[NAME] AS 'TableName'
,[url removed, login to view] as 'Column'
,[url removed, login to view] AS 'Type'
[url removed, login to view] C
[url removed, login to view] tb ON tb.[object_id] = C.[object_id]
[url removed, login to view] T ON C.system_type_id = T.user_type_id
-- [url removed, login to view] TF ON TF.PC_Column = [url removed, login to view]
tb.[is_ms_shipped] = 0
AND tb.[NAME] LIKE '%code_%'
AND [url removed, login to view] = 'SHORT_DESC'
[url removed, login to view] LIKE '%country%'
**Note:** these are the columns to add to this query
Schema TableObj TableName Column Type max_length is_nullable *SHORT_DESC Value (from CODE_ table), *PC_Column (from Transformations table)
1 1826105546 CODE_COUNTRY SHORT_DESC nvarchar 20 0 United States, USA
1 2018106230 CODE_COUNTY SHORT_DESC nvarchar 20 0 Mexico, Mexico
For example, the value in the SHORT_DESC should match the PC_Column
CODE_VALUE_KEY CODE_VALUE SHORT_DESC MEDIUM_DESC LONG_DESC STATUS
1001 1001 Autauga Autauga Autauga A
1003 1003 Baldwin Baldwin Baldwin A
1005 1005 Barbour Barbour Barbour A
1007 1007 Bibb Bibb Bibb A
1009 1009 Blount Blount Blount A
GM_Column Value Note1 F4 PC_Table_Column PC_Table PC_Column Value1 Note2 F10
Ugender M NULL = demographics_gender demographics gender Male NULL NULL
Ugender F NULL = demographics_gender demographics gender Female NULL NULL
Ugender U NULL = demographics_gender demographics gender Unknown NULL NULL
Umarstat D NULL = demographics_marital_status demographics marital_status Divorced NULL NULL
Umarstat M NULL = demographics_marital_status demographics marital_status Married NULL NULL
Umarstat O NULL = demographics_marital_status demographics marital_status Other NULL NULL
Umarstat S NULL = demographics_marital_status demographics marital_status Single NULL NULL
Sytem Tables --I queried system tables to find a common column with user data and don't see a common column to join to the user data tables. Those queries are in the attached file
I'm using SQL Server 2008 R2
Please reply with:
The time of day that you are available. I am available 7am to 3pm UTC-06:00 Central Time US & Canada
If you are able to login remote to test code on a remote desktop like Teamviewer
If you speak english and are available on voice
See attached file for a better question format
18 freelancers are bidding on average $30 for this job
I have 9+ years of experience in SQL Server and .NET Relevant Skills and Experience SQL Server, .NET, JQUERY Proposed Milestones $30 USD - After complition