Getting values from multiple tables in one column

Question

Let's say I have three tables:

table orders:

invoice_ID	customer_ID
202201	1000
202202	2000
202203	3000
202204	4000

table department_north

customer_ID	product	price
4000	VW Rabbit	$5000.00
1000	BMW X5	$15.000

table department_south

customer_ID	product	price
3000	Tesla S	$30.000
2000	BMW X3	$20.000

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

invoice_ID	product_bmw	price_bmw
202201	BMW X5	$5.000
202202	BMW X3	$20.000

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%'

Can't you append OR department_south.product LIKE '%BMW%' or UNION your department tables together as one set and join on this as a subquery or CTE, etc. — Ryan Wilson
– Ryan Wilson, Commented Oct 5, 2022 at 19:45

Paweł Dyl · Accepted Answer · 2022-10-05 19:53:20Z

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%';

JayD · Accepted Answer · 2022-10-05 21:21:15Z

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.

Collectives™ on Stack Overflow

Getting values from multiple tables in one column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related