Combine Two Tables in Select (SQL Server 2008)

Question

If I have two tables, like this for example:

Table 1 (products)

id
name
price
agentid

Table 2 (agent)

userid
name
email

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

How do I join for example SELECT WHERE price < 100?

Answer 1

This is a very rudimentary INNER JOIN:

SELECT
  products.name AS productname,
  price,
  agent.name AS agentname
  email
FROM 
  products
  INNER JOIN agent ON products.agentid = agent.userid

I recommend reviewing basic JOIN syntax and concepts. Here's a link to Microsoft's documentation, though what you have above is pretty universal as standard SQL.

Note that the INNER JOIN here assumes every product has an associated agentid that isn't NULL. If there are NULL agentid in products, use LEFT OUTER JOIN instead to return even the products with no agent.