CODEWITHSUNDEEP
sqlreporting-servicesinner-joinssrs-2012
Jenesis
Jenesis

Reputation: 109

Adding an additional Inner join with mutiple tables SQL

I'm wondering how to inner join customer name-(CUSTNAM) from table rm001 to this query & have it added to my SSRS Report. Could use help adding this. Thanks

I've tried adding after the "from" saleslineitems as an "and" but it broke the SSRS report. 

use n

select distinct  a.[SOP Number]
--, [Item Number]
,  a.[Customer Number], a.[Created Date from Sales Transaction], a.[Primary Shipto Address Code from Sales Line Item]
, a.[City from Sales Transaction], 
,c.city
,case
     when b.CITY <> c.city then 'Cities Do Not Match'
     when c.city = '' then 'Cities do not Match'
     when isnull(c.city,'1') = '1' then 'Cities Do Not Match'
     else ''
end as [validate cities]
,b.USERDEF1 as GP_F
, c.f_number as EZ_F
,case 
     when b.USERDEF1 <> c.f_number then 'Fs do not Match'
     when b.USERDEF1 = '' then 'No F in GP'
     
     else ''
end as [validate Fs]
, c.f_expiration
,case
     when c.f_expiration <= getdate() then ' F EXPIRED '     
     when c.f_expiration <= DATEADD(d,15,getDate()) then 'F expiring soon'
     --when c.f_expiration >= dateAdd(d,61,getdate()) then 'valid F Expiration'
     else ''
end as [valid f date]

--,( select top(1) c.f_number from NBS_BoundBook..contacts where c.f_number = b.userdef1 order by c.uid desc )
--, a.* 
from SalesLineItems a
inner join rm00102 b on a.[customer number] = b.CUSTNMBR and a.[Primary Shipto Address Code from Sales Line Item] = b.ADRSCODE 

left join NBS_BoundBook..contacts c  on Replace(Replace(ltrim(rtrim(b.USERDEF1)),CHAR(10),''),CHAR(13),'') = 
( select top(1) Replace(Replace(ltrim(rtrim(c.f_number)),CHAR(10),''),CHAR(13),'') from NBS_BoundBook..contacts 
     where Replace(Replace(ltrim(rtrim(c.f_number)),CHAR(10),''),CHAR(13),'') = Replace(Replace(ltrim(rtrim(b.USERDEF1)),CHAR(10),''),CHAR(13),'')
     and c.city= b.CITY order by c.uid desc )
where [sop type] = 'Order'
and [Created Date from Sales Transaction] >= dateAdd(d,-3, getDate())
and [Item Tracking Option] in ( 'Serial Numbers' )
order by a.[Customer Number]

Upvotes: 0

Views: 48

Answers (1)

cdsln
cdsln

Reputation: 888

Something like this should work:

    .....
FROM SalesLineItems a
    INNER JOIN rm00102 b
        ON a.[customer number] = b.CUSTNMBR
            AND a.[Primary Shipto Address Code from Sales Line Item] = b.ADRSCODE
    INNER JOIN rm001 cust
    ON cust.[customer number] = a.[customer number]
    LEFT JOIN NBS_BoundBook..contacts c
....

Upvotes: 4

Related Questions