how to read value from two table with difference primary key? |
how to read value from two table with difference primary key? |
akanae |
Mar 7 2014, 12:21 AM
Post
#1
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
greetings to all.
i need a guide or help.. current situation i have two table in database first table - abbreviate data field - words1 and key1 PK - words1 second table - full_word data field -words2 and key2 PK- key2 what i just need is to show words2 from table full_words but the input will read from the words1... how i want to relate this two table?? example: table : abbreviate words1 = NY key1 = 12 table: full_word words2 = New York key2 = 12 if user key in value NY there will show New York as output. or any suggestion link to look up? |
jimlongo |
Mar 7 2014, 01:27 AM
Post
#2
|
This is My Life Group: Members Posts: 1,128 Joined: 24-August 06 From: t-dot Member No.: 16 |
Perhaps some SQL expert will come along, but in the meantime I think you need to do this in 2 steps . . .
get a variable that is the key when the user types in NY then use that variable to get the name of the state. something like this (I'm sure this is not quite right, but should get you somewhere) CODE DECLARE @state int SET @state = key1 from abbreviate where words1 = 'NY' SELECT words2 from full_word WHERE key2 = (' + @state + ') This post has been edited by jimlongo: Mar 7 2014, 01:29 AM |
akanae |
Mar 7 2014, 02:15 AM
Post
#3
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
so i just need to declare first then set the attribute??
|
Brian Chandler |
Mar 7 2014, 03:04 AM
Post
#4
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
current situation i have two table in database first table - abbreviate data field - words1 and key1 PK - words1 second table - full_word data field -words2 and key2 PK- key2 what i just need is to show words2 from table full_words but the input will read from the words1... how i want to relate this two table?? example: table : abbreviate words1 = NY key1 = 12 table: full_word words2 = New York key2 = 12 I can't really understand the point of these tables. Is this right: Table 1 maps numbers (key1) to state abbreviations; Table 2 maps numbers (key2) to state names And I guess that the same number, e.g. 12 represents the same state?? Well, this is a bad way to organise the tables (I think it is how things were done before about 1970!) You need a table with two columns: abbrev and name, where each entry directly maps (e.g.) 'NY' to "New York". Of course it's possible to use your current arrangement (just read them all into an array, if there are only about 50!) -- but it is much better to restart with the table organised properly. HTH |
akanae |
Mar 7 2014, 04:09 AM
Post
#5
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
actually that just an example...
what actually i'm doing... the key for abbrev because there are a lot kind of abbrev word so i just make the key to determine with one value.. the relationship like many words abbrev have one meaning (M:1).. |
Brian Chandler |
Mar 8 2014, 01:06 AM
Post
#6
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
QUOTE actually that just an example...what actually i'm doing...the key for abbrev because there are a lot kind of abbrev word so i just make the key to determine with one value..the relationship like many words abbrev have one meaning (M:1).. I can't really understand this... but I think you mean that the same abbreviation may correspond to more than one word? If so, still no problem -- this is the whole point of associative databases: you have one column for abbreviation and one column for "meaning" (or whatever), and neither of them is unique. I believe there is no problem having a table with no *unique* key (thus no primary key), but if you need to have a primary key, just add an autoincrement column with a "record number", of no particular significance. |
akanae |
Mar 8 2014, 08:31 AM
Post
#7
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
okies...so i just need only one table with two column right??if i want PK for the abbrev just make the column autoincrement??
ok i'll try to do this way... many thank's...^^ |
akanae |
Mar 13 2014, 03:19 AM
Post
#8
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
sorry again...may i ask..
i'm doing just like u said...one table with abbrev column and meaning column... but i have the problem where the data only read one word.. for example... input --> hv b4u but the output only read "before you" only..."have" word did not retrieve.. i'm using sql statement SELECT meaning FROM table WHERE abbrev = abbrev_words; should i use UNION ALL or other way to retrieve the data from same table? |
jimlongo |
Mar 13 2014, 09:22 AM
Post
#9
|
This is My Life Group: Members Posts: 1,128 Joined: 24-August 06 From: t-dot Member No.: 16 |
WHERE hv = abbrev_words OR b4u = abbrev_words
This post has been edited by jimlongo: Mar 13 2014, 09:23 AM |
akanae |
Mar 13 2014, 10:32 AM
Post
#10
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
i have a lot of abbrev word...
so if i'm using that way its means there are a lot of sql statement... |
jimlongo |
Mar 13 2014, 10:50 AM
Post
#11
|
This is My Life Group: Members Posts: 1,128 Joined: 24-August 06 From: t-dot Member No.: 16 |
I guess that all depends on how many inputs to the sql you have doesn't it.
If the person searches for 50 abb then you have to search for 50 If they search for 1 then only 1 is required. |
akanae |
Mar 13 2014, 10:07 PM
Post
#12
|
Novice Group: Members Posts: 24 Joined: 21-February 14 Member No.: 20,404 |
ouh ok. understand.
many thanks...^^ |
Lo-Fi Version | Time is now: 8th June 2024 - 02:18 AM |