Closed

T-SQL to join system tables to values in user tables [url removed, login to view], [url removed, login to view], [url removed, login to view] -- 2

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'

,C.max_length

,C.is_nullable

FROM

[url removed, login to view] C

INNER JOIN

[url removed, login to view] tb ON tb.[object_id] = C.[object_id]

INNER JOIN

[url removed, login to view] T ON C.system_type_id = T.user_type_id

--INNER JOIN

-- [url removed, login to view] TF ON TF.PC_Column = [url removed, login to view]

WHERE

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%'

ORDER BY

tb.[Name]

**Note:** these are the columns to add to this query

query result

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

Transformations Table

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

Skills: Microsoft SQL Server

See more: add magento user sql, database roles current user sql server, manager user sql, sql server oracle tables conversion opensource, download sql plus creating tables, oracle create user sql plus, moodle create user sql, aspnet webbased user sql wizard, sql parent childs tables, php copy sql values, accpac user sql table, sql parent child tables, php sql parent child tables, sql query filter tables, export list user sql, particular user appointments values infragistics forums

About the Employer:
( 273 reviews ) Dallas, United States

Project ID: #14241371

18 freelancers are bidding on average $30 for this job

jagdishbhatt38

I am having more than 8 years of experience in IT industry.I have worked on .net(c#.net,vb.net,asp,asp.net,mvc) with sql server 2005/2008/2012/2014/2016 and oracle(pl/sql). Relevant Skills and Experience I am having m More

$50 USD in 2 days
(18 Reviews)
4.6
mahmoudgamal008

I have more than 5 years of professional and practical experience in SQL and SQL Server I have more than 1800 answers in Stackoverflow(Q&A Site) [url removed, login to view] Relevant Skills an More

$50 USD in 1 day
(9 Reviews)
4.2
pramsweety

Dear, Thank you for posting a job on my favorite technology i.e., Microsoft SQL Server. I have been working with Microsoft SQL server for the past 5 years in ERP, Big Data and Data warehousing environment. I have b More

$35 USD in 1 day
(7 Reviews)
3.2
dat2na

Greetings, I'm professional database expert with 9+ years experience. Currently I'm working as DBA and DB developer in one of the leader company. As you can see in my profile, I have deep experience of same jobs like More

$40 USD in 1 day
(2 Reviews)
2.8
kashivw

I am a veteran (15+ years) SQL Server developer having worked on SQL Server from v4.5 thru 16. I have written thousands of lines of SQL and T-SQL code. I can complete this < 2 hours. Relevant Skills and Experience 2 More

$25 USD in 1 day
(2 Reviews)
2.1
jitendrabaraiya

hi why you are not using store procedure it is quite easy to getting and processing and making query dynamically and showing result. so i think it is better to use sp and get result you want. Relevant Skills and Exper More

$25 USD in 1 day
(0 Reviews)
0.0
preetikaur15

I can do this question.I will try to complete this ques before you pay

$35 USD in 2 days
(0 Reviews)
0.0
nanda2254

I m into database development for the past 7 years. kindly provide the project I will deliver it in time.

$17 USD in 1 day
(0 Reviews)
0.0
srikanthchindam9

List out the _Code tables and prepare script for the result set that you are expecting. Relevant Skills and Experience SQL Server, Good analytical and Programming skills, Have good amount of experience in SQL Serve More

$30 USD in 3 days
(0 Reviews)
0.0
$25 USD in 1 day
(0 Reviews)
0.0
$20 USD in 1 day
(0 Reviews)
0.0
fb57e1f6608abfb

I have around 4+ years experience in data analytics, working on small excel reporting to big DWBI reporting work. I am an expert in developing user-friendly spreadsheets for data analysis Relevant Skills and Experienc More

$25 USD in 1 day
(0 Reviews)
0.0
vw6971103vw

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

$30 USD in 3 days
(0 Reviews)
0.0
jinneshparekh

An IT Professional with rich experience in system analysis, components design, development and testing of client server, Intranet/ Internet, N-tier systems. Relevant Skills and Experience Expert in SQL Proposed Miles More

$29 USD in 3 days
(0 Reviews)
0.0
SaIgor78

I am available 2pm - 7pm your time yone I can login via teamviewer and I'm speaking English. I'm available for voice conversations at time that I wrote above Relevant Skills and Experience I'm using TSQL from MSSQL ve More

$35 USD in 2 days
(0 Reviews)
0.0
forususa

Dear Employer , we are experts we are good at data entry , Timely delivery and quality. we do handle projects in Microsoft BI stack , C# , Power BI ,ASP. we work for Microsoft as consultants and this is first time More

$25 USD in 3 days
(0 Reviews)
0.0
navnitnavnitkuma

A proposal has not yet been provided

$25 USD in 1 day
(0 Reviews)
0.0
$10 USD in 1 day
(0 Reviews)
0.0