Combining Multiple columns from multiple tables into 1 unified table

I have 2 tables, 1 with job information:

Table A:

The other table has remake information with source information from a Job from Table A:

Table B:

The output I am trying to get is a unified list of all jobs, with related information from both tables together (Product/Release Week for each remake in Table B pulled from original job in Table A).

Output Expected:

I have attempted this by doing a left join with case statements as follows:

SELECT CASE WHEN B.[Remake Job Number] is not null THEN B.[Remake Job Number] ELSE A.[Job Number] 
END as [Job Number], A.[Product], CASE WHEN B.[Remake Job Number] is not null THEN 
B.[Remake Quantity] ELSE A.[Quantity] END as [Quantity], A.[Release Week] from [Table A] as A
LEFT JOIN (SELECT [Remake Job Number], [Remake Quantity], [Original Job Number] from [Table B]) as B on A.[Job Number] = B.[Original Job Number]

This ends up giving me a list of all jobs without remakes and the appropriate information, and all remake jobs with correct info, but lacks the original job numbers with remakes with appropriate info. I can see what is happening, but I’m not sure how to add those original job numbers with remakes back into the list (other than some weird IN statement). I also tried to use COALESCE with some similar results as above.

Using SQL Server, any help appreciated.

>Solution :

You can use UNION ALL to combine results from two queries.

First, take everything just from Table A and then combine it with another query that joins information from Table A with Table B.

Here’s how:

SELECT 
  A.[Job Number],
  A.[Product],
  A.[Quantity],
  A.[Release Week]
FROM
  [Table A] AS A

UNION ALL

SELECT
  B.[Remake Job Number],
  A.[Product],
  B.[Remake Quantity],
  A.[Release Week]
FROM
  [Table B] AS B
  INNER JOIN [Table A] AS A ON
    A.[Job Number] = B.[Original Job Number]