Jesper Jesper - 5 months ago 8
SQL Question

Combine Two Tables in Select (SQL Server 2008)

I know this is probably very basic, but I must have been sleeping when they taught that in school :)

If I have two tables, like this for example:

Table 1 (products)


Table 2 (agent)


How do I get a result set from products that include the agents name and email, meaning that
products.agentid = agent.userid

Thank You for your fast answers, but I forgot
. How do I join for example
SELECT WHERE price < 100


Edited to support price filter

You can use the INNER JOIN clause to join those tables. It is done this way:

select, as ProductName, a.userid, as AgentName
from products p
inner join agents a on a.userid = p.agentid
where p.price < 100

Another way to do this is by a WHERE clause:

select, as ProductName, a.userid, as AgentName
from products p, agents a
where a.userid = p.agentid and p.price < 100

Note in the second case you are making a natural product of all rows from both tables and then filtering the result. In the first case you are directly filtering the result while joining in the same step. The DBMS will understand your intentions (regardless of the way you choose to solve this) and handle it in the fastest way.