CODEWITHSUNDEEP
sql-servert-sql
SezMe
SezMe

Reputation: 527

Left Join Not Returning Expected Results

I have a table of Vendors (Vendors):

+-----+-------------+--+
| ID  |   Vendor    |  |
+-----+-------------+--+
|   1 | ABC Company |  |
|   2 | DEF Company |  |
|   3 | GHI Company |  |
| ... | ...         |  |
+-----+-------------+--+

and a table of services (AllServices):

+-----+------------+--+
| ID  |  Service   |  |
+-----+------------+--+
|   1 | Automotive |  |
|   2 | Medical    |  |
|   3 | Financial  |  |
| ... | ...        |  |
+-----+------------+--+

and a table that links the two (VendorServices):

+-----------+-----------+
| Vendor ID | ServiceID |
+-----------+-----------+
|         1 |         1 |
|         1 |         3 |
|         3 |         2 |
|       ... |       ... |
+-----------+-----------+

Note that one company may provide multiple services while some companies may not provide any of the listed services.

The query results I want would be, for a given Vendor:

+------------+----------+
| Service ID | Provided |
+------------+----------+
|          1 |        0 |
|          2 |        0 |
|          3 |        1 |
|        ... |      ... |
+------------+----------+

Where ALL of the services are listed and the ones that the given vendor provides would have a 1 in the Provided column, otherwise a zero.

Here's what I've got so far:

SELECT
    VendorServices.ServiceID,
    <Some Function> AS Provided 
FROM
    AllServices LEFT JOIN VendorServices ON AllServices.ID = VendorServices.ServiceID
WHERE
    VendorServices.VendorID = @VendorID
ORDER BY
    Service

I have two unknowns:

  1. The above query does not return every entry in the AllServices table; and
  2. I don't know how to write the function for the Preovided column.

Upvotes: 1

Views: 214

Answers (1)

forpas
forpas

Reputation: 164069

You need a LEFT join of AllServices to VendorServices and a case expression to get the column provided:

select s.id,
       case when v.serviceid is null then 0 else 1 end provided
from AllServices s left join VendorServices v
on v.serviceid = s.id and v.vendorid = @VendorID

See the demo.

Upvotes: 2

Related Questions