Newbie: Selecting data from multiple tables, Join or Sub-Query?

[cutley]

I'm a novice SQL user, in need of some help. My situation is this...

I have two tables, one is a users table where naturally each user has a unique id (emp_id). The second table contains records that are owned by one specific user, and there is a field named "emp_id" which contains the corresponding "emp_id" from the users table.

So, what I need to do is list all the records in the second table based on a user specified sort order, and then print out the corresponding first and last name from the user table.

Now I could just do a query for all the records in the second table output all the records in a loop, the run another query that looks at the "emp_id" and finds the match in the users table. However, that seems incredibly kludgy, there must be a way to combine these into one smart query.

Any assistance would be greatly appreciated. Thank you in advance!

 

[cutley]

In my original post I said, "The second table contains records that are owned by one specific user..." That may be misleading.

The second table is named "src_main", it has contact records where each contact has an "emp_id" associated with it. The idea is you can tie a user to the contact record, and in this case I want to output an index to the contact list and print out the owner's first and last name which reside in the users table.

I hope this makes sense, I'm trying hard to be clear but...

 

[JamesLean]

This is a classic one-to-many relationship and can be solved with one simple query:

SELECT s.col1, s.col2, u.firstname, u.lastname
FROM src_main s INNER JOIN usertable u ON s.emp_id = u.emp_id
ORDER BY s.col1

The key is the FROM clause where you join the two tables on the emp_id field.