How can I turn this query that uses a join statement into a query that uses a subquery?

I’m given the query

SELECT DISTINCT InvoiceNumber, InvoiceDate, InvoiceTotal
    FROM Invoices JOIN InvoiceLineItems
        ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
    WHERE InvoiceLineItemAmount > 50
    ORDER BY InvoiceTotal

and told to rewrite it using a subquery. I tried writing

SELECT DISTINCT InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Invoices 
WHERE InvoiceLineItemAmount >  <-----------get error saying the column isn't recognized
    (SELECT InvoiceLineItemAmount
     FROM InvoiceLineItems
     WHERE InvoiceLineItemAmount > 50)
ORDER BY InvoiceTotal

but I get an error saying the column isn’t recognized?

Answer

You are trying to access sub-query (or inner query) table’s column outside and hence the error.

Please try this:

SELECT i.InvoiceNumber, i.InvoiceDate, i.InvoiceTotal
    FROM Invoices i
    WHERE i.InvoiceID in (select InvoiceID from InvoiceLineItems where InvoiceLineItemAmount > 50)
    ORDER BY i.InvoiceTotal