how to read value from two table with difference primary key? Options

[akanae]

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]

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]

so i just need to declare first then set the attribute??

[Brian Chandler]

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]

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]

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]

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]

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]

WHERE hv = abbrev_words OR b4u = abbrev_words

This post has been edited by jimlongo: Mar 13 2014, 09:23 AM

[akanae]

i have a lot of abbrev word...
so if i'm using that way its means there are a lot of sql statement...

[jimlongo]

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]

ouh ok. understand.
many thanks...^^