I need inner join for my project FLASK PYTHON

Question

I have some problem with my inner join.

Please take a look for this code.

@app.route('/products', defaults={'page':1})
@app.route('/products/<int:store_id>', methods=['GET'])
def productcheck(store_id):
    if 'loggedin' in session:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute("SELECT * FROM products WHERE store_id=%s", [store_id])
        data = cursor.fetchall()
        sql = "SELECT categories.category_title AS product_category FROM products INNER JOIN categories ON products.product_category = categories.id"
        cursor.execute(sql)
        myresult = cursor.fetchall()
        if data:
            return render_template('productstore.html', data=data, myresult=myresult)
        else:
            return 'Error loading #{id}'.format(id=id)
    else:
        return redirect(url_for('login'))

<table class="table">
  <thead>
    <tr>
      <th scope="col">product name</th>
      <th scope="col">product price</th>
      <th scope="col">category</th>
      <th scope="col">delete</th>
    </tr>
  </thead>
  {% for item in data %}
  <tbody>
    <tr style="font-size: 22px;">
      <th scope="row">{{ item.product_title }}</th>
      <td>{{ item.product_price }}</td>
      <td>{{ "product category should be here" }}</td>
      <td><a href="/delete/{{ item.slug }}" class="fa fa-edit" style="font-size: 34px;"></a></td>
    </tr>
    {% endfor %}

in product table there is categoryid and in category table thereis categoryname.

I want to match this both values and show category name instead of category id

this is products table

+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| store_id            | int(11)          | YES  |     | NULL    |                |
| product_title       | varchar(255)     | YES  |     | NULL    |                |
| product_price       | varchar(30)      | YES  |     | NULL    |                |
| product_count       | int(11)          | YES  |     | NULL    |                |
| product_description | text             | YES  |     | NULL    |                |
| product_category    | int(11)          | YES  |     | NULL    |                |
| image               | varchar(100)     | YES  |     | NULL    |                |
| date                | varchar(30)      | YES  |     | NULL    |                |
| slug                | varchar(100)     | YES  |     | NULL    |                |
| product_link        | varchar(255)     | YES  |     | NULL    |                |
| product_ignore      | int(14)          | NO   |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+
12 rows in set (0.01 sec)

this is categories

+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| store_id             | int(11)          | YES  |     | NULL    |                |
| category_title       | varchar(200)     | YES  |     | NULL    |                |
| category_description | text             | YES  |     | NULL    |                |
| date                 | varchar(30)      | YES  |     | NULL    |                |
| slug                 | varchar(100)     | YES  |     |         |                |
+----------------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

I just fixed it by myself thank you, everybody. the corrected code is down below.

@app.route('/products/<int:store_id>', methods=['GET'])
def productcheck(store_id):
    if 'loggedin' in session:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute("SELECT * FROM products LEFT JOIN categories ON products.product_category = categories.id WHERE products.store_id=%s", [store_id])
        data = cursor.fetchall()
        if data:
            return render_template('productstore.html', data=data)
        else:
            return 'Error loading #{id}'.format(id=id)
    else:
        return redirect(url_for('login'))

Kostas Charitidis Kostas Charitidis · Accepted Answer · 2019-09-18T13:21:37

Try to reverse you from with the inner join, other than that it seems ok:

sql = "SELECT categories.category_title AS product_category" \
      "FROM categories" \
      "INNER JOIN products" \
      "ON products.product_category = categories.id"

I need inner join for my project FLASK PYTHON

1 Answers