join - SQL server query that selects data from another table if it exists

Question

Basically I am trying to pull price from a CRV table in addition to other data. I do want the query to select items whether or not a crv price exists. This is what I have so far:

DECLARE @60daysago DATETIME = DATEADD(DAY,-60,CAST(GETDATE() AS DATE));

select i.itemnum,
itemname,
itemname_extra,
d.description, 
COUNT(CASE WHEN it.datetime > DATEADD(DAY, -90, CAST(GETDATE() AS DATE))
               THEN ii.itemnum END) AS '90 days',
price

from inventory as i
inner join departments as d 
    on d.dept_id= i.dept_id
inner join invoice_itemized ii 
    on ii.itemnum=i.itemnum
join invoice_totals it 
    on it.invoice_number=ii.invoice_number
--inner join inventory_tagalongs it1 on it1.TagAlong_ItemNum=i.itemnum

where Last_Sold >= @60daysago and in_stock != '0' and len(i.itemnum)>4 
or (price >'49.99' and in_stock !='0')  group by i.itemnum, i.itemname, i.itemname_extra, d.description, i.price

The price for the CRVS exists in the inventory table but the inventory_tagalongs tables contains all the items with crvs attached to them. How can I make this query work so that I can add a CRV column and it will populate with crv price if it exists and be blank or null if it does not exist. Thanks so much!

Answer 1

等待大神答复