2019-05-24
SQL Query to look for nulls from two different tables while adding in a new column that states what table its from
源自: dba.stackexchange
问题

I need help with a query that I am trying to make. I have two tables, Orders and Process and I am trying to find instances where the OrderName and ProcessTitle in each table is null and I want to add in a column in the query to state what table it is in.

Orders Table

OrderName                   OrderNumber        OrderStatus
-------------------------------------------------------------
 xx                                   1          Active
 xx                                   2          Active
 bbb                                  3          Active
 abc                                  4          Active
 NULL                                 5          Inactive


Process Table

 ProcessTitle                   ProcessNumber   ProcessStatus
-------------------------------------------------------------
 abc                                  1          Active
 abc                                  1          Active
 db                                   2          Active
 BG                                   3          Inactive
 BG                                   3          Inactive
 Null                                 4          Active
 Null                                 4          Inactive

Here is what I have so far. I queried both tables to show where the OrderName
and ProcessTitle to display where there are nulls by using the union function. 
I am still new to SQL and am still learning but I wasn't sure how to search 
this question. I feel like I should be using a case statement to achive this but how 
would I reference both tables? Thank you in advance.


Select OrderName, 
       OrderNumber, 
       OrderStatus
From Orders
where OrderName is null
UNION
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus
where ProcessTitle is null




答案
1

Is there a reason you can't simply hardcode a literal of the name of the SourceTable in each select?

--Demo setup
Declare @OrdersTable table (
  OrderName VARCHAR(4),
  OrderNumber INTEGER,
  OrderStatus VARCHAR(8)
);

INSERT INTO @OrdersTable
  (OrderName, OrderNumber, OrderStatus)
VALUES
  ('xx', '1', 'Active'),
  ('xx', '2', 'Active'),
  ('bbb', '3', 'Active'),
  ('abc', '4', 'Active'),
  (NULL, '5', 'Inactive');

Declare @ProcessTable table (
  ProcessTitle VARCHAR(4),
  ProcessNumber INTEGER,
  ProcessStatus VARCHAR(8)
);

INSERT INTO @ProcessTable
  (ProcessTitle, ProcessNumber, ProcessStatus)
VALUES
  ('abc', '1', 'Active'),
  ('abc', '1', 'Active'),
  ('db', '2', 'Active'),
  ('BG', '3', 'Inactive'),
  ('BG', '3', 'Inactive'),
  (Null, '4', 'Active'),
  (Null, '4', 'Inactive');

--Solution
Select OrderName, 
       OrderNumber, 
       OrderStatus,
       'OrdersTable' as SourceTable
From @OrdersTable
where OrderName is null
UNION ALL
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus,
       'ProcessTable' as SourceTable
From @ProcessTable
where ProcessTitle is null

| OrderName | OrderNumber | OrderStatus | SourceTable  |
|-----------|-------------|-------------|--------------|
| NULL      | 4           | Active      | ProcessTable |
| NULL      | 4           | Inactive    | ProcessTable |
| NULL      | 5           | Inactive    | OrdersTable  |
SQL Query to look for nulls from two different tables while adding in a new column that states what table its from
查看原网页