How to search for a specific value in a specific table and return matching response from another table with Python

Issue

I have a database called Jarv that has two tables called (COM) and (RES) and each table has three columns like this:

1. (COM) table:

(ID) int , (CName) varchar , (CType) varchar

2. (RES) table:

(ID) int , (RName) varchar , (RType) varchar

I want to get input from user then check if value exist in column CName of (COM) table it returns the (ID) of same row then it go to the other table that is called (RES) and return value of (RName) column with matching (ID), For Example:

Table COM:

ID     |      CName      |      CType     

1      |        Hi       |     Greeting
2      | whats your name |     Greeting
3      |   how are you   |     Greeting

Table RES:

ID     |      RName      |      RType     

1      |      Hello      |     Greeting
2      | My name is Jarv |     Greeting
3      |    I'm great    |     Greeting

So for example user’s input is (Hi) so we have a variable like ( COMM = "Hi" ):

1. Step one:
   search (CName) column inside (COM) table for value that equals (Hi), if found go to step two else –> "Sorry, repeat again"

2. Step two:
   2- return the (ID) value of matching row with (Hi) in (ID) column, if found go to step three else –> "Sorry, repeat again"

3. step three:
   3- search (RName) column inside (RES) table for matching value of (ID) column with (ID) column of (COM) table in last step, if found go to step four else –> "Sorry, repeat again"

4. step four:
   4- return value of (RName) column in the same row as (ID) row into a variable called RESP, if found print(RESP) else –> "Sorry, repeat again"

Solution

If I have understood the question correctly you could try something like this:

def getResponse(userInput):
    # Select id 
    cursor.execute("SELECT ID FROM COM WHERE CName = %s", (userInput, ))
    result = cursor.fetchone()

    # Check if id in DB 
    if result == None:
        return "Sorry, repeat again"
    else:
        # Select RName with COM.ID 
        cursor.execute("SELECT RName FROM RES WHERE ID = %s", (result[0], ))
        result = cursor.fetchone()
        if result == None:
            return "Sorry, repeat again"
        else:
            return result[0]

If it doesn’t matter where it breaks , you can also do it with a subquery, or a JOIN.

Like:

def getResponse(userInput):

    cursor.execute("SELECT RName FROM RES JOIN COM ON COM.ID = RES.ID "\
                   "WHERE CName = %s", (userInput, ))
    result = cursor.fetchone()
    if result == None:
        return "Sorry, repeat again"
    else:
        return result[0]        

Or like:

def getResponse(userInput):

    cursor.execute("SELECT RName FROM RES WHERE ID = "\
                   "(SELECT ID FROM COM WHERE CName = %s)", (userInput, ))
    result = cursor.fetchone()
    if result == None:
        return "Sorry, repeat again"
    else:
        return result[0]        

Answered By – Kartoffelkultur

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0