Combine Two Tables in Select (SQL Server 2008)

by

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.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. SQL update from one Table to another based on a ID match
  3. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  4. What is the difference between using a cross join and putting a comma between the two tables?
  5. Fastest way to determine if record exists
  6. Best way to do nested case statement logic in SQL Server
  7. How to select only the first rows for each unique value of a column?
  8. how does SELECT TOP works when no order by is specified?
  9. Is it possible to write single query on two table which are not connected to each other?
  10. select yesterday date in where condition
  11. Import CSV file into SQL Server
  12. How to create a table from select query result in SQL Server 2008 [duplicate]
  13. Are there any disadvantages to always using nvarchar(MAX)?
  14. Creating an index on a table variable
  15. Is there a LastIndexOf in SQL Server?
  16. Get all table names of a particular database by SQL query?
  17. Turn off constraints temporarily (MS SQL)
  18. OR is not supported with CASE Statement in SQL Server
  19. How do I execute a stored procedure once for each row returned by query?
  20. Case in Select Statement
  21. T-SQL: Selecting Column Based on MAX(Other Column)
  22. SQL dot notation
  23. Split function in SQL Server 2008
  24. PIVOT query on Distinct records
  25. How to read the last row with SQL Server
  26. Performance of SQL Server 2005 Query
  27. How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
  28. How to set bool value in SQL
  29. How can I group by date time column without taking time into consideration
  30. Finding node order in XML document in SQL Server

Leave a Comment