0

I have a table with multiple transaction types. Currently I'm only pulling the adjustments and payments, but I am needing to match the IDs of the types to their descriptions. For example, my transactions table has paytype_id and adjustment_id which range 1-100 for each. I have two other tables dbo.paytype and dbo.adjustments that have the distinct paytype_id and adjustment_id along with the pay_desc and adj_desc fields that describe what kind they are. Issue I'm running into is that any given transaction will only have a paytype_id or an adjustment_id but not both. So if I attempt to join one table, then the other I lose the NULL values of the secondary IDs

This query will pull the paytype_id descriptions but will remove any transactions with adjustment_id due to the payment_id being NULL for them.

SELECT 
    t.tran_num, t.resp_party_id, t.Total, 
    t.paytype_id, t.adjustment_id, t.clinic, 
    t.date_entered, p.pay_desc
FROM 
    adjpay_vw t 
CROSS JOIN 
    paytype p
WHERE 
    (t.paytype_id = p.paytype_id AND t.clinic = p.clinic)

So I'm wondering how I can pull both the adj_desc from dbo.adjustments and the pay_desc from dbo.paytype

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
OM Asphyxiate
  • 329
  • 1
  • 5
  • 14

1 Answers1

2

By having your join condition in the WHERE, it forces it to behave like an INNER JOIN. Instead of using the WHERE, add it to the JOIN condition and flip to a LEFT JOIN.

SELECT t.tran_num, t.resp_party_id, t.Total, t.paytype_id, t.adjustment_id, t.clinic, t.date_entered, p.pay_desc, adj.adj_desc, ISNULL(p.pay_desc,adj.adj_desc) [Description]
FROM adjpay_vw t
LEFT JOIN paytype p ON t.paytype_id=p.paytype_id AND t.clinic=p.clinic
LEFT JOIN dbo.adjustments adj ON adj.adjustment_id = t.adjustment_id

Also, since I wasn't sure your view contained the adjustment description, I've added a join for that and added both descriptions and a new columns that shows the description no matter if it was an adjustment or payment.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51