Sql-server – How to perform JOIN and only use single record for each instance on 2nd table

join;sql server

I am trying to perform a join from table 1. In table 2 there could be multiple matches on the join, but I only want to use each match once and only once. How do I do this? Here is my data set:

Products

Product_Plan_ID      Product_Plan_Creation_Date
===============      =========================
123-3241             1/2/2018
123-3242             1/15/2018
123-3242             1/17/2018
123-3243             1/18/2018
123-3244             2/2/2018
123-3245             2/5/2018

AND…

Product_Delivery

Product_Plan_ID      Product_Plan_Delivery_Date
===============      =====================
123-3242             1/20/2018
123-3242             1/22/2018

Here is code to generate tables and data set…

CREATE TABLE Products
(
Product_Plan_ID varchar(25),
Product_Plan_Creation_Date date
)

create table Product_Delivery
(
Product_Plan_ID varchar(25),
Product_Plan_Delivery_Date date
)

INSERT INTO Products 
select '123-3241', '1/2/2018'
union all
select '123-3242', '1/15/2018'
union all
select '123-3242', '1/17/2018'
union all
select '123-3243', '1/18/2018'
union all
select '123-3244', '2/2/2018'
union all
select '123-3245', '2/5/2018'

INSERT INTO Product_Delivery
SELECT '123-3242', '1/20/2018'
UNION ALL
SELECT '123-3242', '1/22/2018'

In my query, I want to join the Products table to Product_Delivery and for each row in Products, I want to join it to Product_Delivery matching on the Product_Plan_ID and where the Product_Plan_Delivery_Date is within 30 days of the Product_Plan_Creation_Date. The kicker is, if I find a match in Product_Delivery, I only want that match to be used a single time. The way I have it now, I'm ending up with duplicates on Product_Plan_Delivery_Date 1/20/2018 below. Here's my query so far:

select Products.Product_Plan_ID, Products.Product_Plan_Creation_Date, 
t2.Product_Plan_Delivery_Date from Products
CROSS APPLY (
    SELECT TOP 1 t.Product_Plan_ID, t.Product_Plan_Delivery_Date 
    FROM Product_Delivery t
    where t.Product_Plan_Delivery_Date between 
    Products.Product_Plan_Creation_Date and dateadd(dd, 30, Products.Product_Plan_Creation_Date)
and Products.Product_Plan_ID = t.Product_Plan_ID
) t2

The problem with the above is I end up with a duplicate match for Product_Plan_ID 123-3242 on Product_Plan_Delivery_Date, 1/20/2018. How would I write this so I have Product_Plan_ID 123-3242 match first on 1/20/2018, then 2nd on 1/22/2018?

My desired result set would be this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/22/2018

However…I'm getting this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/20/2018

Thanks in advance.

Best Answer

For future posts, it is better to provide CREATE TABLE statements and INSERT statements. It is also beneficial to add an expected result set. What I guess you are trying to do is to pair Product_Plan_Creation_Date and Product_Plan_Delivery_Date. You can create an ordering number with a WINDOW function:

SELECT Product_Plan_ID, Product_Plan_Creation_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Creation_Date) AS RN
FROM Products

and similar for Product_Delivery:

SELECT Product_Plan_ID, Product_Plan_Delivery_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
FROM Product_Delivery

Now you can use the ordering number to join these two derived tables:

SELECT A.Product_Plan_ID, A.Product_Plan_Creation_Date
     , B.Product_Plan_Delivery_Date
FROM (
    SELECT Product_Plan_ID, Product_Plan_Creation_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                              ORDER BY Product_Plan_Creation_Date) AS RN
    FROM Products
) AS A
JOIN (
    SELECT Product_Plan_ID, Product_Plan_Delivery_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
    FROM Product_Delivery
) AS B
    ON A.Product_Plan_ID = B.Product_Plan_ID
    AND A.RN = B.RN

Related Solutions

Mysql – How to simulate a table join on itself

You'll need to first create a list of every product_number and date combination. You can do this using a CROSS JOIN of your table:

select distinct p.product_number, d.date
from yourtable p
cross join yourtable d;

See SQL Fiddle with Demo. This will create a list of data similar to:

| PRODUCT_NUMBER |                            DATE |
|----------------|---------------------------------|
|            100 |  January, 01 2010 00:00:00+0000 |
|            200 |  January, 01 2010 00:00:00+0000 |
|            100 | February, 01 2010 00:00:00+0000 |

You will then use the above query and LEFT JOIN to your table to return the final result:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join yourtable d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Giving a final result of:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |

The LEFT JOIN returns all rows from your list of products and dates regardless of whether a matching row exists in the other table.

This could also be written as:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
( 
  -- list of every date
  select distinct date
  from yourtable 
) d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. This may have better performance depending on your table size.

Now if you wanted to return a list of all dates, regardless of whether or not they appear in the table, then I would suggest creating a table of dates. This table would be used in a similar manner to create a list of all dates/products which you would then join.

The table would be similar to:

CREATE TABLE dates
    (`date` datetime)
;

INSERT INTO dates
    (`date`)
VALUES
    ('2010-01-01 00:00:00'),
    ('2010-02-01 00:00:00'),
    ('2010-03-01 00:00:00'),
    ('2010-04-01 00:00:00'),
    ('2010-05-01 00:00:00')
;

You'd then use the following query to get the list of dates/products:

select distinct p.product_number, d.date
from yourtable p
cross join dates d

And finally, you would join that back to your table:

select 
  pd.product_number,
  pd.date,
  t.value
from
(
  -- list of every product/date
  select distinct p.product_number, d.date
  from yourtable p
  cross join dates d
) pd
left join yourtable t
  on pd.date = t.date
  and pd.product_number = t.product_number
order by pd.product_number, pd.date;

See SQL Fiddle with Demo. Or an alternative:

select 
  p.product_number,
  d.date,
  t.value
from
(
  -- list of every product
  select distinct product_number
  from yourtable 
) p  
cross join
 dates
  d
  -- then join to the table
left join yourtable t
  on  d.date = t.date
  and p.product_number = t.product_number
order by p.product_number, d.date ;

See SQL Fiddle with Demo. Again this may have better performance based on the table size. Using this type of solution, you'd return all dates even those not in your table:

| PRODUCT_NUMBER |                            DATE |  VALUE |
|----------------|---------------------------------|--------|
|            100 |  January, 01 2010 00:00:00+0000 |      1 |
|            100 | February, 01 2010 00:00:00+0000 |      1 |
|            100 |    March, 01 2010 00:00:00+0000 |      1 |
|            100 |    April, 01 2010 00:00:00+0000 | (null) |
|            100 |      May, 01 2010 00:00:00+0000 | (null) |
|            200 |  January, 01 2010 00:00:00+0000 |      1 |
|            200 | February, 01 2010 00:00:00+0000 | (null) |
|            200 |    March, 01 2010 00:00:00+0000 |      1 |
|            200 |    April, 01 2010 00:00:00+0000 | (null) |
|            200 |      May, 01 2010 00:00:00+0000 | (null) |

How to join the latest previous record with SQL

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists:

SELECT P.product_id AS product_id,
      ,P.deal_dt    AS deal_dt
      ,C.deal_dt    AS previous_deal_dt
      ,C.cancel_dt  AS previous_cancel_dt
FROM   product_shipping p
LEFT   JOIN product_shipping C ON C.product_id = P.product_id
                              AND C.cancel_reason_no IN (2, 4)
                              AND C.cancel_dt < P.deal_dt
WHERE  P.deal_reason_no IN (1, 3, 5)
AND    P.deal_dt BETWEEN '2014-04-01 00:00:00' AND '2014-04-11 00:00:00'
AND    NOT EXISTS (
   SELECT 1
   FROM   product_shipping C1
   WHERE  C1.product_id = C.product_id
   AND    C1.cancel_reason_no IN (2, 4)
   AND    C1.cancel_dt < P.deal_dt
   AND    C1.cancel_dt > C.deal_dt
   );

Assuming (product_id,cancel_dt) to be UNIQUE, else you need tiebreaker criteria to be unambiguous. This way you do not need GROUP BY, because only one (or no) row is left-joined.

Also assuming deal_dt and cancel_dt are supposed to come from the same cancelled deal.

This would be simpler with the window function lag(), but your version does not seem to support that according to the discussion under the duplicate post.

Best Answer

Related Solutions

Mysql – How to simulate a table join on itself

How to join the latest previous record with SQL

Related Question