Ali's Photography Space…

Left Join: All the Data in the Category
Right Join: All the Data in the Products
Inner Join: All the Data that in Both Tables.
Left Join Only Data in Category Table and NOT in Product Table.
Right Join: Only data in Product Table and NOT in Category Table.
Full Outre: All the Records in both Tables
Full Outre: All the data from the Category Table that are NOT linked to any Product, AND all the data in Product Table that has NO Category.

First: let’s talk about the Tables,we will create two Tables (Category and Products) each will be very simple, the Category will have two fields [c_id, and cat_name],
the Products will have [p_id, pro_name, cat_id]. (cat_id is the foreign key that connicting the two tables). Some test data will be inserted in both Tables so we can demonstrate the SQL Commands. I create a Dictionary storing information/summary about the project named about_the_project the user can read it in run time.

Coding: We will do the fowlloing:
1. Set a Database for the Project.
2. Writing the codes to Create the Tables.
3. Writing the codes to insert some test Data in the Tables.
4. Creating a Menu for the Project.
5. Writing the Functions to Apply the SQL Join commmands.

NOTE: All the code will be avilable in the DOWNLOAD page.

Let’s import some lybraries, we will need this code:

import sqlite3, os

And here is the code to set the data base and create the connection.
# Create the data-base and name it as Share_S_System.
db_conn = sqlite3.connect (“sql_join_test.db”)
# set the connection.
c = db_conn.cursor()

First function is to create the Tables:

 # Function to create the Tables.

def create_tables ():
    
    # CREATE TABLE:
    category_t = "CREATE TABLE if not exists category_t (c_id INTEGER PRIMARY KEY AUTOINCREMENT, cat_name text)" 
    c.execute(category_t) 
    db_conn.commit()     
    
    product_t = "CREATE TABLE if not exists product_t (p_id INTEGER PRIMARY KEY AUTOINCREMENT, p_name text, cat_id integer)" 
    c.execute(product_t) 
    db_conn.commit()     

Now we will insert some Test data.

 # Function to insert some Data into the Tables

def insert_sample_data():
    
    #First we will call the Function to Crete the Table.
    create_tables ()
    print('\n   Category Table has been Created. ')
    print('\n   Product Table has been Created. ')
    
    # Insert into Category Table
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"Mobile Phone"})
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"Labtop"}) 
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"Ext. HD"}) 
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"CCTV Camera"})     
    db_conn.commit()

    # Insert into Product Table.
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"Note 9","cat_id":"1"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"iPhone 12 pro","cat_id":"1"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"HP","cat_id":"2"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"Toshiba 1T HD","cat_id":"3"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"iMac","cat_id":None})
    db_conn.commit()
    
    print('\n   Sample Date Inserted in both tables.\n   Select 9 from the Main Menu to see the Data')
    
    input('\n      ... Press any key to continue.  > ')

Now, let’s write the function for the Main Menu, it will return the user_choice.. here is the code..

 # Main Menu Function.

def main_menu():
    os.system('clear')
    print("\n==========[ Main Menu ]==========")    
    print(' 1. About this Project.')
    print(' 2. All Categories. (Left Join: All in Category Only).')
    print(' 3. All Products. (Right Join: All in Product Only).')
    print(' 4. Only Categories that linked to a Products. (Inner Join: Only if in Ta AND Tb).')
    print(' 5. All Catigories that are NOT linked to a Product. (Left Join: Only in Category AND NOT in Product .')
    print(' 6. All Products that has NO Category. (Right Join: Only in Product AND NOT in Category.')
    print(' 7. All Categories and Products. (Full Outer Join in Both Table)' )
    print(' 8. All Categories that are NOT linked to any Product, and All Products that has NO Categotry. (Full Outer Join NOT in Both Table)')
    print(' 9. Show the Data') 
    print(' 11. Setting: Create the Tables and Insert Some Sample Data. Run Only One Time.')
    print(' 99. Exit.')

    user_choice = input("\n Select from the Menu: > ") 
    return user_choice

The coming part of the code is calling the functions and running the SQL commands based on the user choice. Here is the Main-code-body..

 # The Main section of the Application..

while True :
    user_select = main_menu()

    if user_select == '1' :
        show_data('cat') 

    if user_select == '2' :
        show_data('prod') 

    if user_select == '3' :
        os.system('clear')
        print('\n   First: All the Data in Category and Product ')
        show_data('both','inside')
        print("\n==========[ Show Date: INNER JOIN ]==========\n")
        print('   Inner Join: Only the Data that exist in Category AND Product).')
        print('\n   The SQL Statment:\n    select category_t.c_id, product_t.p_name from category_t inner join product_t on category_t.c_id == product_t.cat_id\n\n ')
        c.execute ("select category_t.c_id, category_t.cat_name, product_t.p_name from category_t inner join product_t on category_t.c_id == product_t.cat_id  ") 
        innerJ_list = c.fetchall() 
     
        print('   [OUTPUT]:\n       Categories Name      |',' '*2,'Products Name    ')
        for innerJ_data in (innerJ_list): 
            print('       ',innerJ_data[1],'.'*(25 - len(str(innerJ_data[1]))),innerJ_data[2]) 
            
        print('\n   We can see that the INNER JOIN Command fetched the records of Products that has a Category.\n   iMac Not listed Because it has No Category Data.')         
        input('\n\n      ...Press any key to continue.  > ') 
    
   if user_select == '4' :
        os.system('clear')
        print('\n   List of All the Data in Category and Product ')
        show_data('both','inside')
        print("\n==========[ Show Date: LEFT JOIN, Only Data in Categoty Table ]==========\n")
        print('   The SQL Statment:\n     select category_t.c_id, category_t.cat_name, product_t.p_name from category_t\n     left join product_t on category_t.c_id == product_t.cat_id\n     where product_t.cat_id is null\n')
        c.execute ("select category_t.c_id, category_t.cat_name, product_t.p_name from category_t left join product_t on category_t.c_id == product_t.cat_id where product_t.cat_id is null") 
        leftJ_list = c.fetchall()     
        print('   [OUTPUT]:\n       Categories Name      |',' '*2,'Products Name    ')
        for leftJ_data in (leftJ_list): 
            print('         ',leftJ_data[1],'.'*(25 - len(str(leftJ_data[1]))),leftJ_data[2]) 
              
        print('\n   We can see that the LEFT JOIN Command fetched all Categories that are not used/linked to/with any Product.\n   CCTV Camera is Not linked. So We can Say: We Do Not have any CCTV Camera Products.')         
        input('\n\n      ...Press any key to continue.  > ') 

    if user_select == '5' :
    ....
# Just to save the time, i will not post all the code here, you can see all the code and download
# the .py source code from the download page.

Here are some screen shot of the output ..

The Main Menu

Output Screen for Option 3: Only Categories that linked to a Products. (Inner Join: Only if Data in Category AND Product)

End of the Post, and all the code is available to download from the Download page.