SQL Server How can I create a 0 value when joining two different tables?

6rqinv9w 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(145)

I have two tables, one for planned hours in production and another one for actual hours measured in production.

Table PlannedHours for Order = 'abc123' looks like this:

| Operation  | Order  | Planned_hrs |
| ---------- | ------ | ----------- |
| Activity 1 | abc123 | 1           |
| Activity 2 | abc123 | 2           |

Table ActualHours for Order = 'abc123' can look like this:

| Operation  | Order  | Actual_hrs  |
| ---------- | ------ | ----------- |
| Activity 1 | abc123 | 1,75        |
| Activity 2 | abc123 | 2,2         |

Then I would like to join and get this table:

| Operation  | Order  | Planned_hrs | Actual_hrs |
| ---------- | ------ | ----------- | ---------- |
| Activity 1 | abc123 | 1           | 1,75       |
| Activity 2 | abc123 | 2           | 2,2        |

However, in this particular example Table ActualHours for Order = 'abc123' looks like this:

| Operation  | Order  | Actual_hrs  |
| ---------- | ------ | ----------- |
| Activity 2 | abc123 | 2,2         |

because Activity 1 was conducted elsewhere and therefore not measured.

I would like to join the tables and get this result:

| Operation  | Order  | Planned_hrs | Actual_hrs |
| ---------- | ------ | ----------- | ---------- |
| Activity 1 | abc123 | 1           | 0          |
| Activity 2 | abc123 | 2           | 2,2        |

I have tried this code:

SELECT
b.[Operation]
b.[Order]
b.[Actual_hrs]
e.[Planned_hrs]
from Actual_hours b
left join Planned_hours e
on b.[Order] = e.[Order]
where b.[Order] = 'abc123'
group by b.[Order], b.[Operation], e.[Planned_hrs]
order by b.[Order] asc

but then I get the following result:

| Operation  | Order  | Planned_hrs | Actual_hrs |
| ---------- | ------ | ----------- | ---------- |
| Activity 1 | abc123 | 1           | 1,75       |
| Activity 2 | abc123 | 2           | 1,75       |

so I tried adding a condition on the join:

SELECT
b.[Operation]
b.[Order]
b.[Actual_hrs]
e.[Planned_hrs]
from Actual_hours b
left join Planned_hours e
on b.[Order] = e.[Order] and b.[Operation] = e.[Operation]
where b.[Order] = 'abc123'
group by b.[Order], b.[Operation], e.[Planned_hrs]
order by b.[Order] asc

Which partially works because I get the following result:

| Operation  | Order  | Planned_hrs | Actual_hrs |
| ---------- | ------ | ----------- | ---------- |
| Activity 2 | abc123 | 2           | 1,75       |

but, unfortunately, then I am missing Activity 1.

sql-server

来源：https://stackoverflow.com/questions/75628208/how-can-i-create-a-0-value-when-joining-two-different-tables

2条答案

按热度按时间

au9on6nz1#

You are doing From Actual_hours LEFT JOIN Planned_hours . That means for each row of Actual_hours you get lines of Planned_hours if exists.

I think in your case you want the exact opposit like that :

SELECT
    e.[Operation],
    e.[Order],
    e.[Planned_hrs],
    ISNULL(b.[Actual_hrs], 0)
from Planned_hours e
left join Actual_hours b
    on b.[Order] = e.[Order] and b.[Operation] = e.[Operation]
where e.[Order] = 'abc123'
order by e.[Order] asc

赞(0）回复(0）举报 2023-03-07

5m1hhzi42#

I am using sql fiddler. Here are the steps that I used to reproduce and solve your problem.

Create the tables.

create table planned
(
  Operation1 varchar(32),
  Order1 varchar(32),
  Planned_hrs varchar(32)
 );
 
create table actual
(
  Operation1 varchar(32),
  Order1 varchar(32),
  Actual_hrs varchar(32)
);

Add the data.

-- Add data
insert into planned values ('Activity 1', 'abc123', '1');
insert into planned values ('Activity 2', 'abc123', '2');
-- insert into actual values ('Activity 1', 'abc123', '1,75');
insert into actual values ('Activity 2', 'abc123', '2,2');

Since we did not measure line 3, it did not make it into the table. On the other hand, if you have the data but want to treat it different add a column such as "off-site".

-- Select data
select p.*, isnull(a.actual_hrs, 0) as hours
from planned as p 
left join actual as a
on p.Operation1 = a.Operation1 and
p.Order1 = a.Order1

The technique is a left join with an isnull() on the field that has missing data. This will end up with our zero value.

Screen shot of sql fiddle.