SQL - How can I exclude a duplicate line generated by a join?-CodePudding

I'm facing some challenges with some logic I need to create using SQL. I'm not an expert in SQL, so maybe it could be very simple but until now I didn't figure it out.

I have to join Table A with Table B so that I can later use one of the values from Table B to filter on. Table A consists of purchasing organisations related to a specific material. A purchasing organisation can purchase at multiple plants. Which plants the purchase organisation can purchase from is stored in Table B.

The lead time is the time it takes for the material to get from the plant to the porg, these vary per material.

Table A

| porg | matnr | plant | lead time |
|------|-------|-------|-----------|
|HAM   |12345  |BER    |5          |
|HAM   |12345  |       |9          |
|BER   |54321  |       |15         |

Table B

| porg | plant |
|------|-------|
|HAM   |BER    |
|HAM   |DOR    |
|HAM   |MUN    |
|BER   |HAM    |
|BER   |HAN    |
|BER   |WOL    |

When the plant is empty in Table A, it means that the that lead time is applicable for all plants. When it's populated, it has to use that specific lead time. The issue I'm having is that I have no idea how to exclude the plant in the join that's already populated. Because joining the two tables on all the rows where plant is empty will create a duplicate record with the wrong lead time.

Required output:

| porg | matnr | plant | lead time |
|------|-------|-------|-----------|
|HAM   |12345  |BER    |5          |
|HAM   |12345  |DOR    |9          |
|HAM   |12345  |MUN    |9          |
|BER   |54321  |HAM    |15         |
|BER   |54321  |HAN    |15         |
|BER   |54321  |WOL    |15         |

The follow SQL code I currently have, but this does not give me the correct output.

SELECT a.porg, a.leadtime, a.plant, b.plant 
FROM table a as a 
LEFT OUTER JOIN table b as b
ON a.porg = b.porg AND a.plant = ''

CodePudding user response：

It seems like you just need to exclude a result set. Why don't you add a WHERE b.plant IS NOT NULL in you clause at last:

SELECT a.porg, a.`matnr`, a.`lead_time`, a.plant, b.plant 
FROM tableA AS a 
LEFT OUTER JOIN tableB AS b
ON a.porg = b.porg AND a.plant = ''
WHERE b.`plant` IS NOT NULL

Am I missing something?

CodePudding user response：

I think I understand that you are trying to:

Obtain a list of the plants and their lead times for each purchase organization, and
If a plant is mentioned in Table A as having a specific lead time that will be used, but the other plants will use the default value

If I am correct, I would think the following query would do what you are hoping for:

SELECT 
    a.porg, 
    a.matnr, 
    CASE
        WHEN a.plant = '' THEN def.plant
        ELSE a.plant
    END AS plant, 
    CASE
        WHEN a.plant = '' THEN def.leadtime
        ELSE a.leadtime
    END AS leadtime
FROM table_a a
OUTER APPLY (
SELECT b.porg, b.plant, a1.leadtime
FROM table_b b
LEFT JOIN table_a a1
    ON a1.porg = b.porg
    AND ISNULL(a1.plant, '') = ''
) def

CodePudding user response：

Try this one query

SELECT 
b.porg, 
CASE WHEN a.matnr IS NULL THEN alt.matnr ELSE a.matnr END AS matnr, 
b.plant, 
CASE WHEN a.leadtime IS NULL THEN alt.leadtime ELSE a.leadtime END AS leadtime
FROM b
LEFT JOIN a ON a.porg = b.porg AND a.plant = b.plant
LEFT JOIN a alt ON alt.porg = b.porg AND alt.plant IS NULL

Demo

CodePudding user response：

;with cteTabA as ( --Set up sample data in CTEs
    SELECT * FROM (
        VALUES ('HAM', 12345, 'BER', 5)
        , ('HAM', 12345, '', 9)
        , ('BER', 54321, '', 15)
      ) as TabA(porg, matnr, plant, leadTime)
), cteTabB as (
    SELECT * FROM (
        VALUES ('HAM', 'BER'), ('HAM', 'DOR'), ('HAM', 'MUN')
        , ('BER', 'HAM'), ('BER', 'HAN'), ('BER', 'WOL')
      ) as TabB(porg, plant)
), cteDefaults as (--The real query begins here, find the "default" lead time
    SELECT * FROM cteTabA as D --The value where no plant is specified
    WHERE D.plant = '' --Can also test for NULL if that's a possibility
)
SELECT B.* --Show the possible purchase locations
    , COALESCE(A.leadTime, D.leadTime) as leadTime --Show the specific lead time if it exists,
    --otherwise show the default lead time
FROM cteTabB as B --All the possible purchase locations
    --Join in any specific lead times
    LEFT OUTER JOIN cteTabA as A on B.plant = A.plant AND B.porg = A.porg 
    --join in the default lead times
    LEFT OUTER JOIN cteDefaults as D on B.porg = D.porg

EDIT: Show sample output

porg	plant	leadTime
HAM	BER	5
HAM	DOR	9
HAM	MUN	9
BER	HAM	15
BER	HAN	15
BER	WOL	15