[SOLVED] How to check if a field that can be created but wasn't exists?

[nooneclose]

Good morning, I am trying to figure out if it is possible to check if these invoice lines exist or not. 

Here are the table and column name: INVOICELINE.INVOICELINENUM (a required field if created)

Here is a picture of what I am talking about. (Do not worry about security. the picture is from a demo test site so all information is fake)

 

is there any way to check if these fields exist or not? (they do not exist unless the user clicks on "New Row")

Example: line 11 does not exist right now. How would I go about to see if it did or not? 

This is what I have so far:

SELECT DISTINCT iv.invoicenum, iv.description
FROM invoice     AS iv
JOIN invoiceline AS ivl ON iv.invoicenum = ivl.invoicenum AND iv.siteid = ivl.siteid 
/* = 'nothing' and yet somehow not null? */
WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL)

I get 0 results where I should get more than 0. 

Edited November 27, 2019 by nooneclose
Solved

[jchd]

6 minutes ago, nooneclose said:

WHERE iv.invoicenum NOT IN (SELECT invoicelinenum FROM invoiceline WHERE invoicelinenum IS NOT NULL)

You're comparing invoicenum and invoicelinenum that is oranges and shoes.

[nooneclose]

@jchd my bad that is a typo it should say (SELECT invoicenum FROM invoiceline WHERE invoicelinenum IS NOT NULL)

[nooneclose]

Solved it. The only problem is that they "don't exist" without a line number. (nonpersistent) The code does work its just a database issue at this point. 

Like always here is the final code:

SELECT distinct invoicenum, description
FROM invoiceline 
GROUP BY invoicenum, description  
HAVING SUM(invoicelinenum) < 1
ORDER BY invoicenum

 

Edited January 3, 2020 by nooneclose
More Accurate information