August 13, 2013 at 12:04 pm
I have table1 with multiple columns, the Status column has 3 possible values (1,2,3). I created another table(2) named status_lut (below). When I query table1 I need it to display the StatusName. How would I create the lookup table?
StatusNum StatusName
1 Submitted
2 Active
3 Done
Thank You
August 13, 2013 at 12:33 pm
nuchbcc (8/13/2013)
I have table1 with multiple columns, the Status column has 3 possible values (1,2,3). I created another table(2) named status_lut (below). When I query table1 I need it to display the StatusName. How would I create the lookup table?StatusNum StatusName
1 Submitted
2 Active
3 Done
Thank You
I am a bit confused. Isn't what you posted your lookup table? Are you really trying to ask how you would display the StatusName from this table when joining to another table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 12:33 pm
I assume that you are trying to match values of StatusNum from table2 and status from table1
select
b.StatusName
from querytable.dbo.table1 a
join querytable.dbo.table2 b
on a.status = b.StatusNum
August 13, 2013 at 12:49 pm
I tried the JOIN query separately and received the output that I needed but when I added it to the existing query, it failed.
I guess what I'm trying to ask is do I need to setup PK and FK? If yes do I create the FK from table1, set the PK table to be table2 pointing to StatusName column and FK table to be table1 pointing to StatusID column?
Thank You
August 13, 2013 at 12:55 pm
nuchbcc (8/13/2013)
I tried the JOIN query separately and received the output that I needed but when I added it to the existing query, it failed.I guess what I'm trying to ask is do I need to setup PK and FK? If yes do I create the FK from table1, set the PK table to be table2 pointing to StatusName column and FK table to be table1 pointing to StatusID column?
Thank You
No you are not required to establish a foreign key relationship between these tables. However if you do you would NOT have be on the StatusName column it would be on the value column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 1:29 pm
Thank you for being patient.
To create a FK so the query output would replace the value with the StatusName, from SMS;
On table1 right-click on Keys, select New Foreign Key...
Click on Add and expand the "Tables And Columns Specification"
For "Primary key table", select the lookup table and StatusName column
For "Foreign key table", table1 is hardcoded, select the value column
Are the above steps correct?
Thank You
August 13, 2013 at 1:37 pm
nuchbcc (8/13/2013)
Thank you for being patient.To create a FK so the query output would replace the value with the StatusName, from SMS;
On table1 right-click on Keys, select New Foreign Key...
Click on Add and expand the "Tables And Columns Specification"
For "Primary key table", select the lookup table and StatusName column
For "Foreign key table", table1 is hardcoded, select the value column
Are the above steps correct?
Thank You
You should probably read up on what a foreign key and why they are useful. I think you are confused about what they do. They are not some sort of magical tool to replace values or something. It is used to enforce data integrity between the two tables. The datatypes of the two columns MUST be the same.
http://www.w3schools.com/sql/sql_foreignkey.asp
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 1:47 pm
I knew it couldn't be that easy. I thought I was doing something wrong in creating PK-FK. I'll do more reading. Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply