Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Lookup Table Setup? Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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
Post #1483886
Posted Tuesday, August 13, 2013 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483898
Posted Tuesday, August 13, 2013 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 11:09 AM
Points: 1, Visits: 9
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
Post #1483900
Posted Tuesday, August 13, 2013 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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
Post #1483904
Posted Tuesday, August 13, 2013 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483909
Posted Tuesday, August 13, 2013 1:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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
Post #1483932
Posted Tuesday, August 13, 2013 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483935
Posted Tuesday, August 13, 2013 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:53 PM
Points: 12, Visits: 67
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.
Post #1483940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse