[SOLVED] How to append results from multiple tables in SQL?

How to append results from multiple tables in SQL?

I have 3 tables in singlestore (Memsql) that I want to query and have the results appended.

Orders

part_number	received_qty	update_ts
partA	10	Monday

PullSheet

part_number	pull_qty	return_qty	pull_date
partA	3	0	Tuesday

VarianceSheet

part_number	pull_qty	return_qty	pull_date
partA	1	2	Wednesday

I would like a stored procedure that takes a part_number, limit, and offset input. Query these tables for part_number return all the results sorted by their timestamps and then apply limit and offset. Result should be like below.

Result

event_name	part_number	received_qty	pull_qty	return_qty	date
order	partA	10	null	null	Monday
pullsheet	partA	null	3	0	Tuesday
variancesheet	partA	null	1	2	Wednesday

What would be the best way to achieve this? I looked into Unions but that requires the tables to have the same number of columns. That is not the case for my tables.

Solution

If the table schema is fixed and not going to be changed, you can do the following:

SELECT 
  'order' as event_name
  ,part_number
  ,received_qty
  ,NULL as pull_qty
  ,NULL as return_qty
  ,update_ts as date
FROM Orders

UNION ALL

SELECT 
  'pullsheet' as event_name
  ,part_number
  ,NULL as received_qty
  ,pull_qty
  ,return_qty
  ,pull_date as date
FROM PullSheet

UNION ALL

SELECT 
  'variancesheet' as event_name
  ,part_number
  ,NULL as received_qty
  ,pull_qty
  ,return_qty
  ,pull_date as date
FROM VarianceSheet

Using UNION ALL here for fast performance as there is no need to check for duplicity (event_name will be distinct for each table at least)