Not sure what to put into subject but here goes..

If a DB has multiple tables, but all the tables are linked, how do I call a table and the information that is linked to that table??

ex:
table 1 - user info
table 2 - company they belong to and it's info
table 3 - products (products can be associated with more then one company, it's a general list of products)

Any suggestions?

Thanks, newbie here to this forum...

breeze76

This seems to be very generic. But still ...

What kind of Database are you using? Access/SQL server/something else?

You will have to look for the Key Field using which they are interlinked and have to pull in the relevant data based on the same.

Eg: if you are trying to list the users who are using a product and belonging to what company. You may query something like

Select UserInfo.UserName, Company.CompanyName, Products.ProductName
From UserInfo, Company, Products
Where UserInfo.CompanyId=Company.CompanyId and Products.CompanyId=Company.CompanyId.

Userinfo, Company, Products are all TableNames.
UserName, CompanyName, ProductName are all Columns that you wanted to list.
As per the above Query CompanyId is the Column using which the 3 tables are linked for relevant data.

Probably, you will have to go through some Books on this, to learn the concepts. Also post your questions more clear, so that you would be able to get answers for it at lesser hops.

Hope this gives you in some way.

Please feel free to ask if you have anything else.

Cheers!


-Vijay G

Here is a relations in my database:

Table USERS: AutoID,FIRM_NAME, USER_NAME, USER_ID, PASSWORD, EMAIL, NOTES
Table FIRMS: FIRM_ID, FIRM_NAME, SPECIAL_NOTES
Table CASE_USERS: CaseID, UserName, FirmID
Table CASE_NAME: ID, CASE_NAME

1 - One user belongs to one firm.
2 - A firm can only have a user once in it's firm but more then one user in a firm.
3 - Cases can have more then one user in a case and more then one firm in a case, also, a user can belong to more then one case as well as a firm could belong to more then one case.

How confusing, eh. So, how would I query the above say:

A - If I want a user, when the results are shown, it will have user info, the firm he belongs to and any cases he is involved in.
B - If I want a firm, when the results a shown, it will have the firm info, users in that firm with the related info for the users and any cases that the users are involved in.
C - If I want a case, when the results are shown, it will have the users and their info, firms and their info shown.

See how confused I am over all this, would you have any help for the overloaded mind...

breeze76

I think you would find things a bit simpler if you were to alter your table structures slightly. Are you able to do that, or is it "too late"?

USERS table:
1. I would not have FIRM_NAME in this table, but instead I would have FIRM_ID. It is much easier to link tables based on a numeric ID than a string Name.
2. why do you have AutoID in this table? Isn't USER_ID enough to uniquely identify each record?

CASE_USERS table:
1. similarly here I would store USER_ID not the USER_NAME, which properly only belongs in the USERS table.
2. why do you need FIRM_ID in here?

CASE_NAME table:
1. I assume the ID field here is the CaseID?
2. rename the field CASE_NAME, its not good to have fields with the same name as the table they're in - very confusing. Just call it CaseName or something like that.

Then your queries would be something like this (I show only one field from each table as an example, the other fields could be added using the same syntax of TABLENAME.FieldName):
A - SELECT USERS.USER_NAME, FIRMS.FIRM_NAME, CASE_NAME.CaseName
FROM USERS INNER JOIN FIRMS
ON USERS.FIRM_ID = FIRMS.FIRM_ID
INNER JOIN CASE_USERS
ON USERS.USER_ID = CASE_USERS.USER_ID
INNER JOIN CASE_NAME
ON CASE_USERS.CASE_ID = CASE_NAME.CaseID

can you see that the ON clause of the JOIN expression sopecifies the relationship between the tables?

The other 2 queries would be similar syntax. Just list the fields that you want and join together the tables that contain those fields, specifying the relationship in the ON clause. (Sometimes you have to add in joins for tables from which you don't want any fields just to get the relationships right, for example in the above query you have to add in the CASE_USERS table in order to get to the fields in CASE_NAME.)

hth
Phil