Please Help...Urgent!

How can I write a Select Statement from two tables that are related?

for example that i want to select the department name from a dept table
for a speacial User..where the user is in a table users and has a foriegn key which is the dept no.

i want to display the departmnet name in a text box..lets say txtdept

Thanx in advance for you help.

try this:
assume that table dept has deptcode and description as fields and
table user has deptcode and name.

the user name is stored in the string variable called usename.

and the querry is,

"select b.description from user a, dept b where a.name = '" & username & "' and b.deptcode = a.deptcode"

create a recordset using this querry and use the value of that recordset as the text property of the textbox, say, txtdept.text = recordset.fields(0).

does it work ?

shinu.

Hi,

If you have two tables and have a foreign key in one, then you can use the INNER JOIN statement in your SQL. The solution that shinu gave is an embeded inner join. They used to make those in the early days. Anyway:



This is the same as Shinu, but more efficient on the database. Since the join statement has a much better hashing technique, where the join in the where statements results in a table scan.

If you have any questions, feel free to ask them.

Grtz.©

M.

P.s. It is not my intention to step on toes, don't regard this message as being so.