Let's say I have three tables:

table orders:

table department_north

table department_south

Wanted Result
A table with invoice_id, a new column with all cars that contain '%BMW%', a new column with the attached price

I figured out how to get the results for one department table but can't find a statement for both.

    SELECT DISTINCT orders.invoice_ID, 
                    department_north.product AS product_BMW,
                    department_north.price   AS price_BMW
    FROM orders
    JOIN LEFT department_north
       ON department_north.customer_ID = order.customer_id
    JOIN LEFT department_south
       ON department_south.customer_ID = order.customer_id
    WHERE department_north.product LIKE '%BMW%'

CodePudding user response：

I would UNION ALL all departments. See following example:

DECLARE @orders TABLE
(
    invoice_ID varchar(20),
    customer_ID int
);
INSERT @orders VALUES
(202201, 1000),
(202202, 2000),
(202203, 3000),
(202204, 4000);

DECLARE @department_north TABLE
(
    customer_ID int,
    product nvarchar(20),
    price decimal(15,2)
);
INSERT @department_north VALUES
(4000, 'VW Rabbit', 5000),
(1000, 'BMW X5', 15000);

DECLARE @department_south TABLE
(
    customer_ID int,
    product nvarchar(20),
    price decimal(15,2)
);

INSERT @department_south VALUES
(3000, 'Tesla S', 30000),
(2000, 'BMW X3', 20000);

WITH AllDepartments AS
(
    SELECT *
    FROM @department_north
    UNION ALL
    SELECT *
    FROM @department_south
)
SELECT invoice_ID, product, price
FROM @orders O
JOIN AllDepartments D ON O.customer_ID=D.customer_ID
WHERE product LIKE '%BMW%';

CodePudding user response：

I would use union all like Paweł Dyl's answer above, but would create a single department table and create an extra column, called location or similar and put an 'S' for south and an 'N' for north into it as per below:

create table #department
(
    customer_ID     int
    , product       varchar(64)
    , price         decimal(15,2)
    , "location"    varchar(64)     -- to allow for other locations
)
;

insert into #department values (4000, 'VW Rabbit', 5000.00, 'N');
insert into #department values (1000, 'BMW X5', 15.000, 'N');

insert into #department values (3000, 'Tesla S', 30.000, 'S');
insert into #department values (2000, 'BMW X3', 20.000, 'S');

This means that you are just using the one department table and you have the additional 'location' column for adding east or west if need be. This will reduce the need to create a new database table for each new location added to your list. You could expand this to include city and/or state or whatever depending on the range of the data but you should aim to use only one table for this purpose.

Creating multiple tables based purely on location would not be recommended and think, what would you do if there were many locations e.g. 50 or more? It would be a nightmare to manage this code by creating a separate table for each location.