Linking Two Tables

I have two tables in a database. userid and usercreditsIn userid I have all the fields that identify the user (name, address etc.)In my usercredits table I have the following fields:IDNUMProgramNameCamp#CompletionDateAmountDatePmtSentPmtMethodCommentscreditIDWhat I would like to understand about my table structure, is how do I associate my credits tothe correct user. If I have done this correctly, there is also a field in the first table that is called IDNUM where each user has a unique number to identify who they are.When someone is logged into the site I need to display the values from the usercredits table that are only THEIR credits.Am I starting this off correctly?

Yes, you're perfectly right.In your user-table you would probably have one entry per user only, so that the ID is a unique value.Then, when the user logs in you could get all the entries from the usercredits table that contain his userid ("SELECT Amount FROM usercredits WHERE IDNUM = 'user'") and sum them up or whatever. It might be a good idea to keep count of the current total credit number in a separate table and rename the current "usercredit" table to "usertransactions" or so. This would save you the extra effort of summing up the credits total.Seems fine to me otherwise,Ruben

You need one field unique field (where the value is unique) in both table so that they can be linked together. For example you can use IDNUM as your primary key in both tables and link the tables using that field.

You need one field unique field (where the value is unique) in both table so that they can be linked together. For example you can use IDNUM as your primary key in both tables and link the tables using that field.

 

Yes I set it up as the primary key in both. So it sounds like I'm on the right track here.

do you guys have any tutorials on this as id like to learn some of this

do you guys have any tutorials on this as id like to learn some of this

There's tons of them in the web. Type in your favourite search engine: "SQL tutorial" and you'll get bunch of really good tutorials with lots of examples. Once you know the basics the DBMS's developer's online reference manual is probably the most handy tool to use. I've been doing database applications for years now but still need to check things from dev.mysql.com/doc regularly.
Edited August 27, 2006 by Hercco (see edit history)

I was hoping you guys had posted some here.. i tried looking up a few thru the search.. but no luck.. and yeah i guess i will have to use th eweb to look themup.. thnx

I have two tables in a database. userid and usercredits
In userid I have all the fields that identify the user (name, address etc.)

In my usercredits table I have the following fields:

IDNUM
ProgramName
Camp#
CompletionDate
Amount
DatePmtSent
PmtMethod
Comments
creditID
What I would like to understand about my table structure, is how do I associate my credits tothe correct user. If I have done this correctly, there is also a field in the first table that is called IDNUM where each user has a unique number to identify who they are.

When someone is logged into the site I need to display the values from the usercredits table that are only THEIR credits.

Am I starting this off correctly?

Ok. Your table structure looks fine. You say that you IDNUM as the common field between two tables. As long as it is unique in bothe the tables your table structure will alllow you to join the tables perfectly. Are you planning to have a row in "usercredits" for every user in userid? May be it doesn't matter, but if you don't have any row in usercredits for a particular userid, you'll have to handle the exception of "Now Rows Found".

Yes, you are starting off correctly. You can use IDNUM for joining the tables.
Edited August 30, 2006 by SP Rao (see edit history)

So this is a one to many relationship
One user can multiple credits records in the table.

Here should be somewhat of an answer to your design:

User Table
-----------
userid (primary key)
Name
address
phone
(etc)

Credit Table
-------------
creditID (primary key)
userid (foreign key)
Camp#
CompletionDate
Amount
DatePmtSent
PmtMethod
Comments


So in order to call an entry from the credit table you must have both keys (foreign and partial primary key).

SQL would look something like this

SELECT U.name, C.completiondate, C.amount, C.datepmtsent, C.pmtmethod, C.commentsFROM users UJOIN credit C ON U.userid=C.useridORDER BY C.datepmtsent ASC

I guess that would give u all the payments made by a user. Obviously if you wanted a single payment you would have to identify the appropriate where clauses and give the correct arguements.

Lots of reading on this topic but I guess this would be as good of a place as any. I suggest that you pay particular attention to the ERD's as they will probably explain most of the text if you understand them.

http://forums.xisto.com/no_longer_exists/

Well, as ginginca was asking for some guidance, there are a lot of stuff in the web. Somehow I feel that you don't need to search particularly for MySQL tutorials alone. It's better you learn the basica of SQL first. (May be little bit about RDBMS concepts).

 

Now, there are million sites on net which will provide you information about SQL. The one which is particularly good for beginners is http://www.sqlcourse.com/. This site offers online interactive tutorial where you can test what you are learning too.

 

Moreover, you will get info and updates on almost all well known databases MySQL, Oracle, MS SQL etc.

I think this site will definitely help you in learning the SQL basics.

 

You may also try W3Schools tutorials for SQL SQL Tutorial

 

For RDBMS concepts and data modelling, any google search will help you.

Happy programming

Edited August 31, 2006 by SP Rao (see edit history)

Well, as ginginca was asking for some guidance, there are a lot of stuff in the web. Somehow I feel that you don't need to search particularly for MySQL tutorials alone. It's better you learn the basica of SQL first. (May be little bit about RDBMS concepts).

 

Now, there are million sites on net which will provide you information about SQL. The one which is particularly good for beginners is http://www.sqlcourse.com/. This site offers online interactive tutorial where you can test what you are learning too.

 

Moreover, you will get info and updates on almost all well known databases MySQL, Oracle, MS SQL etc.

I think this site will definitely help you in learning the SQL basics.

 

You may also try W3Schools tutorials for SQL SQL Tutorial

 

For RDBMS concepts and data modelling, any google search will help you.

Happy programming

 

Thanks for all the feedback!

Hi guys, I have some question for you. From many articles that I've read. I only found about joining two tables. But I didn't find about joing more than two tables, three tables, and even more!.

Is joining more than two tables same as joining more that 2 tables?. I little confuse about it. If joining 2 tables we simply using query like this:

SELECT table1.column1, table1.column2, table1.column3 FROM table1, table2 WHERE table1.column2='VALUE'

Help is very appreciate!

How to link two tables in sql using primary and foriegn key-reply by shofiya