0

I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with values. I want to be able to compare and match which values are in the pre-loaded tables with the user table. For example: Here is a link to the example tables enter image description here

Now, I want to be able to compare each row in the Pre-Loaded Table to each row in the Users Table. They both have the Barcode column in common to be able to compare. As a result, during the query process, it should check each row: 1234 - milk - 1 (those columns are equal ) 5678 - butter - 1 ( those columns are equal) 9876 - sugar - 1 (those columns are equal) 1100 - - 1 ( this barcode does not exist in the Users Table)

so when a Barcode, in this case, 1100 doesn't exist in the Users Table, the code should print: You don't have all the items for the Pre-Loaded Table.

so far I have this:

import sqlite3 as sq


connect = sq.connect('Food_Data.db')
con = connect.cursor()

sql = ("SELECT Users_Food.Barcode, Users_Food.Item,     Recipe1.Ham_Swiss_Omelet FROM Users_Food INNER JOIN Recipe1 ON Users_Food.Barcode = Recipe1.Barcode WHERE Recipe1.Ham_Swiss_Omelet = '1'")
con.execute(sql)
data = con.fetchall()
print("You can make: Ham Swiss Omelet")
formatted_row = '{:<10} {:<9} {:>9} '
print(formatted_row.format("Barcode", "Ingredients", "Availability"))
for row in data:
    print(formatted_row.format(*row))
    #print (row[:])
    #connect.commit()
3
  • Why is the column named "Apples"? And why are all values 1? Can there ever be some other value? And how would that affect this problem? – CL. Mar 19 '19 at 8:49
  • That is just apart of the problem and no it won't affect the answer. – Abdul-Aziz Muhammad Mar 19 '19 at 21:17
  • So you want an algorithm that either prints "You don't have all the items", or nothing? – CL. Mar 20 '19 at 8:27
0

I figured it out. I was able to do a FULL OUTER JOIN to display what is missing from one table but included in another table.

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.