I have a SQL Server table that includes invoices and another table that includes the lines of the invoices. I’m trying to grab in the lines ones a Price amount on the line to add to my query. my data looks like this (simplified as there is tens of columns in each table and thousands of lines)

Invoice table:

InvNumber | ShipCost
----------+---------
 1        |  500
 2        |  1000

Invoice_line table:

InvNumber  | Product | GL | Price
-----------+---------+----+------
1          |  ABC    | 2  | 10
1          |   Z     | 1  | 50
2          |   X     | 2  | 15

I need to get a query that will grab the price on the invoice line table based on the GL account and add it to my query so in my example if the GL is 1 I need to have that price added to my query.

I created a query with a LEFT JOIN, but when I use it, it created a line where the price is NULL and another line, so I’m missing a little something

Here’s my query:

SELECT DISTINCT
    InvNumber, 
    ShipCost,
    CASE
        WHEN Invoice_line.GL = '1' 
            THEN Invoice_line.Price 
            ELSE NULL
    END AS Price
FROM 
    Invoice
LEFT JOIN 
    Invoice_line ON Invoice.InvNumber = Invoice_line.InvNumber

This is the result that I get now:

InvNumber | ShipCost | Price
----------+----------+-------
 1        |  500     | 50
 1        |  500     | NULL
 2        |  1000    | NULL

Expected result

InvNumber | ShipCost | Price
----------+----------+-------
 1        |  500     | 50
 2        |  1000    | NULL

I tried a SELECT DISTINCT but that didn’t change anything.

Also played with different type of JOIN but nothing gave me what I was expecting

Not sure what I’m missing to get the result that I’m looking for