CODEWITHSUNDEEP
mysqlsqlms-access
KateLO
KateLO

Reputation: 67

SQL match between two tables and further match between columns

I have two tables: Master Data and Sample Data.

Master Data has columns "Key", "from date", "to date", "PayTM"

Sample Data has columns "Key", "creation date", "PayTS"

I'm trying to get the code to do this:

  1. For every "key" match between two tables >

  2. Determine if "creation date" falls between "from date" and "to date" >

  3. IF YES, show that match/record (from Sample Data table) including PayTM and PayTS. IF NO, dont show the record. (And go through all of the matches performing steps 2 and 3)

This is what I have so far, but it's showing records that don't match...I tried replacing WHERE NOT EXISTS with EXISTS but it's giving me an error. My sql coding skills are very basic and I'm struggling with trying to make the code work past this for some reason.

SELECT cd.*
FROM [Sample Data] as cd
WHERE NOT EXISTS (SELECT 1
              FROM [Master Data] as md
              WHERE cd.Key = md.Key AND
                    md.[Creation Date] BETWEEN md.From Date and md.To Date
             );

Upvotes: 0

Views: 390

Answers (2)

snickholas
snickholas

Reputation: 51

I think this should handle it. 

select
        x.key_
       ,x.paytm
       ,x.payts
from(
    select
            md.key   as key_
           ,cd.paytm as paytm
           ,cd.payts as payts
           ,case when md.creation_date between md.from_date and md.to_date
                 then 'goodie'
                 else 'baddie'
            end as the_test  

    from
            [master data] md
    inner join
            [sample data] cd
            on cd.key = md.key
)x
where
        x.the_test != 'baddie'

Upvotes: 0

MKR
MKR

Reputation: 20095

Your problem can be solved by simple join. Something like:

SELECT cd.* , md.*
FROM [Sample Data] as cd, [Master Data] as md
 WHERE cd.Key = md.Key AND
                    cd.[Creation Date] BETWEEN md.[From Date] and md.[To Date];

Note: The above query is pseudo query just to high-light idea.

Upvotes: 1

Related Questions