CODEWITHSUNDEEP
sqlsql-serverselect
Jesper
Jesper

Reputation: 999

Combine Two Tables in Select (SQL Server 2008)

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?

Upvotes: 13

Views: 77941

Answers (6)

abdelgrib
abdelgrib

Reputation: 1140

If you don't want to use inner join (or don't have possibility to do it!) and would combine rows, you can use a cross join :

SELECT *
FROM table1
CROSS JOIN table2

or simply

SELECT *
FROM table1, table2

Upvotes: 0

Vikram K
Vikram K

Reputation: 2686

This is my join for slightly larger 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;

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Edited to support price filter

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

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 by a WHERE clause:

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 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.

Upvotes: 26

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

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)

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

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

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270609

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.

Upvotes: 7

Related Questions