How to get data from a row if a value in a different table is null-CodePudding

I have a database I want to get data from. In order to get the not yet edited files, I want to create a csv file which lists all the files if the age is NULL. This is the code I have written:

def make_csv():
    c,conn=connect()
    #get the id and name from the SERIES table if the age in the SERIES_METADATA table is null
    query="SELECT ID,NAME FROM FILES WHERE FILE_METADATA.AGE IS NULL"
    series_id={'series_id':[],'series_name':[]}
    c.execute(query)
    for row in c:
        series_id['series_id'].append(row[0])
        series_id['series_name'].append(row[1])
        #series_add.append(series_id)
        #print(series_id)
    conn.close()
    df=pd.DataFrame(series_id)
    df.to_csv('files_id-new.csv',index=False,header=True)

However, when I execute the code, I get this error message:

Traceback (most recent call last):
  File "/home/USER/GitHub/Program/dbcon.py", line 146, in <module>
    make_csv()
  File "/home/USER/GitHub/Program/dbcon.py", line 24, in make_csv
    c.execute(query)
sqlite3.OperationalError: no such column: FILE_METADATA.AGE

I know that something must be wrong, as the table FILE_METADATA exists, as well as the column AGE

did I write it wrong or is this kind of operation not possible?

Any Answer is appreciated, thanks in advance.

CodePudding user response：

I assume that in the table FILE_METADATA there is a column, say file_id, which is a foreign key referencing the column id in the table FILES.

In such a case, you can join the tables:

SELECT f.ID, f.NAME 
FROM FILES f INNER JOIN FILE_METADATA m
ON m.file_id = f.ID
WHERE m.AGE IS NULL;

or, with the operator IN:

SELECT ID, NAME 
FROM FILES
WHERE ID IN (SELECT file_id FROM FILE_METADATA WHERE AGE IS NULL);