CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
barsan
barsan

Reputation: 2491

How to join two tables without primary key or unique key?

I have two tables named LocalVSDB and TemporaryVSDB. Both tables have the same columns:

  LocalVSDB: msisdn,activateDate
  TemporaryVSDB: msisdn,activateDate

But both tables also have duplicate rows for MSIDSN

I need to join these two tables. My intended result looks like this: 

    MSISDN     LocalActivateDate    TemporaryActivateDate Datediff
  60103820251    2013-12-14          2013-10-05              70
  601111000254   2013-12-14          2013-10-05              70
  601111000254   2013-12-18          2013-09-10              80

But, since there are duplicate MSIDSNs, I am getting duplicate rows when I join. For example there are 6 rows for certain MSISDN in each table so when I am joining I am getting total 36 rows for that MSISDN.

I am joining using the following query:

   SELECT t.msisdn,t.activateDate AS VSDB_Activate_Date,
          l.activateDate AS Local_Activate_Date,
          DATEDIFF(D,l.activateDate,t.activateDate) AS date_Diff
   FROM temporaryVSDB2  t
   INNER JOIN LocalVSDB  l ON t.msisdn = l.msisdn
   WHERE  t.activateDate >  l.activateDate

Please help me how can I get 6 rows for 6 MSISDN?

Thanks in advance.

Upvotes: 0

Views: 3406

Answers (3)

RajeshK
RajeshK

Reputation: 461

You can use your own query adding group by clause provided msidn and activateDate produce unique row. 

    SELECT t.msisdn,t.activateDate AS VSDB_Activate_Date,
    l.activateDate AS Local_Activate_Date,
    DATEDIFF(D,l.activateDate,t.activateDate) AS date_Diff
    FROM temporaryVSDB2  t INNER JOIN LocalVSDB  l ON t.msisdn = l.msisdn
    WHERE  t.activateDate >  l.activateDate
    group by t.msisdn, t.activateDate

Upvotes: 0

Kevin Cook
Kevin Cook

Reputation: 1932

SELECT m.MSIDN, m.ActiveDate, t.ActiveDate, DATEDIFF(DAY, m.ActiveDate, t.ActiveDate) Duration
FROM LocalVSDB m
OUTER APPLY
(
    SELECT TOP 1 d.MSIDN, d.ActiveDate
    FROM TemporaryVSDB d
    WHERE d.ActiveDate > m.ActiveDate
    ORDER BY d.ActiveDate
) t

This would find the nearest partner record and duration (the last record will have a null partner though)

Upvotes: 1

Vulcronos
Vulcronos

Reputation: 3456

The problem is:

where  t.activateDate >  l.activateDate

That means one row in table one can join to all six rows in table two. You either need to change this to an = or just get a single row from the second table based on certain criteria.

Upvotes: 1

Related Questions