The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> how to read value from two table with difference primary key?
akanae
post 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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Mar 7 2014, 01:27 AM
Post #2


This is My Life
*******

Group: Members
Posts: 1,125
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
akanae
post 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??
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Mar 7 2014, 03:04 AM
Post #4


Jocular coder
********

Group: Members
Posts: 2,298
Joined: 31-August 06
Member No.: 43



QUOTE(akanae @ Mar 7 2014, 02:21 PM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
akanae
post 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)..


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Mar 8 2014, 01:06 AM
Post #6


Jocular coder
********

Group: Members
Posts: 2,298
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
akanae
post 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...^^
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
akanae
post 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?

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Mar 13 2014, 09:22 AM
Post #9


This is My Life
*******

Group: Members
Posts: 1,125
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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
akanae
post 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...
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Mar 13 2014, 10:50 AM
Post #11


This is My Life
*******

Group: Members
Posts: 1,125
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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
akanae
post 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...^^
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 23rd October 2019 - 07:44 PM