CODEWITHSUNDEEP
sqlsql-server
developer
developer

Reputation: 1675

SQL join master details table

I have the following two tables in SQL server:

Product:

SELECT Id, Name From Product

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

Order:

SELECT Id, Order_Date, QTY From Order

x------x--------------------x-------x
|  Id  |   Order_Date       |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

I would like to get a table which contains list of all products and if there is any order placed for the product.

Example:

x------x--------------------x
| Id   |        Has_Order   |
x------x--------------------x
|  1   |          1         |
|  2   |          1         |
|  3   |          0         |
x------x--------------------x

I tried left outer join but it includes all the rows from Order table. What is the most efficient way to write this SQL query?

Upvotes: 0

Views: 162

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option to consider

Select ID 
      ,Has_Order = max(Flg)
 From  (
        Select ID,Flg = 0 From [Product]
        Union All
        Select Distinct ID,Flg = 1 From [Order]
       ) src
 Group By ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use exists in a subquery:

select p.*,
       (case when exists (select 1 from orders o where o.id = p.id)
             then 1 else 0
        end) as order_exists_flag
from products p;

For performance, you want an index on orders(id). I would expect this to be the fastest approach.

Upvotes: 2

Related Questions