Join two tables in Select (SQL Server 2008)

Question

If I have two tables, for example, like this:

Table 1 (products)

id name price agentid 

Table 2 (agent)

 userid name email 

How can I get a result set from products that include the agent name and email address, i.e. products.agentid = agent.userid ?

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

Answer 1

Edited to support price filter

You can use the INNER JOIN to join these tables. This is done as follows:

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

Another way to do this is with the WHERE :

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

Note that in the second case, you make a natural product of all the rows from both tables and then filter the result. In the first case, you directly filter the result, joining the same step. The DBMS will understand your intentions (no matter how you decide to solve it) and process it in the fastest way.

Answer 2

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 that you consider the basic syntax and concepts of JOIN . Here's a link to the Microsoft documentation , although what you have above is pretty universal as standard SQL.

Note that the INNER JOIN here assumes that each product has an associated agentid that is not NULL. If products have a NULL agentid , use LEFT OUTER JOIN to even return products without an agent.

Answer 3

 select p.name productname, p.price, a.name as agent_name, a.email from products p inner join agent a on (a.userid = p.agentid) 

Answer 4

This is my connection for a bit large tables in Prod.Hope, it helps.

 SELECT TOP 1000 p.[id] ,p.[attributeId] ,p.[name] as PropertyName ,p.[description] ,p.[active], a.[appId], a.[activityId], a.[Name] as AttributeName FROM [XYZ.Gamification.V2B13.Full].[dbo].[ADM_attributeProperty] p Inner join [XYZ.Gamification.V2B13.Full].[dbo].[ADM_activityAttribute] a on a.id=p.attributeId where a.appId=23098; 

Answer 5

 select ProductName=p.[name] , ProductPrice=p.price , AgentName=a.[name] , AgentEmail=a.email from products p inner join agent a on a.userid=p.agentid