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.

CodePudding user response：

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]

CodePudding user response：

You can use UNION ALL to combine the result of two queries. The first one corresponds to tablea and the second query would require a join of both tables.

For example:

select * from tablea
union all
select
  b.job_number,
  a.product,
  b.remake_quantity,
  a.release_week
from tableb b
join tablea a on a.job_number = b.original_job_number