sql - JOIN two tables but only return rows from Table 1 that match Table 2 -

good day have 2 tables need join , transfer excise tbl , value entry.

transfer excise tbl: no must match item no in value entry table. did comparison items not in transfer excise in value entry , found few.

transfer excise tbl:

starting date           no_                  excise location location code unit rate                               excise type code unit of measure code litre conversion factor ----------------------- -------------------- --------------- ------------- --------------------------------------- ---------------- -------------------- --------------------------------------- 2013-02-28 00:00:00.000 600011263            nonbond         ~duty paid    2.70000000000000000000                  uwnepack         litres               1.33333000000000000000   2014-02-27 00:00:00.000 600011263            nonbond         ~duty paid    2.87000000000000000000                  uwnepack         litres               1.33333000000000000000   2015-02-26 00:00:00.000 600011263            nonbond         ~duty paid    3.07000000000000000000                  uwnepack         litres               1.33333000000000000000   2016-02-25 00:00:00.000 600011263            nonbond         ~duty paid    3.31000000000000000000                  uwnepack         litres               1.33333000000000000000 

value entry table:

item no_             location code gen_ bus_ posting group invoiced quantity -------------------- ------------- ----------------------- --------------------------------------- f00330               vini          exsa                    -10.00000000000000000000   f00331               vini          exsa                    -30.00000000000000000000   f00332               vini          exsa                    -40.00000000000000000000   

i want write query exclude duplicates script below still creates duplicates. pk item no , fk location code. see on transfer excise table each year new unit rate supplied specific item , location

select distinct a.[starting date],       b.[posting date],      b.[item no_],       b.[invoiced quantity],       a.[litre conversion factor],      a.[unit rate] ,      a.[location code],       a.[excise location],      a.[excise type code],      a.[unit of measure code] [transfer excise tbl] join [spier live$value entry] b on a.[no_] = b.[item no_]   b.[posting date] > '2013-02-26 ' , b.[location code] = a.[location code] , b.[gen_ bus_ posting group] in ('loca','exsa') , b.[posting date] >= a.[starting date] , b.[invoiced quantity] <>0 

first of all, there wrong [value entry] table.

1) in query refer [posting date] column, there no such column in example data.

now, if have understood scenario, think problem related how join lines 2 tables.

i more lines expect because join each line in [value entry] lines in [transfer excise tbl] [starting date] older, not last (valid) line.

to solve problem should pre-calc period of validity of [transfer excise tbl] line finding [end date] of each line, , will
join b.[posting date] between a.[starting date] and a.[end date]

final query like:

;with enddates (-- add [end date] [transfer excise tbl]     select t1.*, isnull([end date], convert(date, '9999-12-31', 121)) [end date]     [transfer excise tbl] t1     outer apply (         select min([starting date]) [end date]         [transfer excise tbl]         [starting date] > t1.[starting date]     ) t2 ) select distinct a.[starting date],       b.[posting date],      b.[item no_],       b.[invoiced quantity],       a.[litre conversion factor],      a.[unit rate] ,      a.[location code],       a.[excise location],      a.[excise type code],      a.[unit of measure code] [enddates] join [spier live$value entry] b on a.[no_] = b.[item no_] , b.[posting date] between a.[starting date] , a.[end date]  b.[posting date] > '2013-02-26 ' , b.[location code] = a.[location code] , b.[gen_ bus_ posting group] in ('loca','exsa')     , b.[invoiced quantity] <> 0 

it should return number of rows expect

i hope helps