CODEWITHSUNDEEP
pythonsqlsqlite
skwolvie
skwolvie

Reputation: 139

How to select the distinct values of three sql tables and perform inner join with python sqlite3?

I'm trying to perform an inner join of distinct values of three tables from an SQLite DB. I tried multiple times and failed. Please guide me.

Below is a pseudo-code of what I'm trying to achieve

sql = ''' 
SELECT DISTINCT lesson_id, question_id FROM lesson_practice_questions as lpq

INNER JOIN 
SELECT DISTINCT topic_id, lesson_id FROM chapter_lessons as cl
WHERE cl.topic_id==2 
ON cl.lesson_id = lpq.lesson_id 

INNER JOIN 
SELECT DISTINCT question_id, subject_id, question_type_id, knowledge_type_ids complexity_level FROM questions as q
ON q.question_id = lpq.question_id;'''

cur.execute(sql)

Many Thanks to @eshirvana for taking the time to help out!.

Perfect Solution with ambiguous error resolution for future stackoverflow reference:

sql = '''SELECT
    lpq.lesson_id, cl.lesson_id,
    topic_id,
    q.question_id, lpq.question_id,
    subject_id,
    question_type_id,
    knowledge_type_ids,
    complexity
FROM  lesson_practice_questions as lpq
INNER JOIN chapter_lessons as cl on cl.topic_id = 2 and cl.lesson_id = lpq.lesson_id
INNER JOIN questions as q ON q.question_id = lpq.question_id;'''

Upvotes: 2

Views: 132

Answers (1)

eshirvana
eshirvana

Reputation: 24568

here is the right sql syntax , however you need to provide sample data and desired output if this is not the right output :

SELECT
    lpq.lesson_id,
    question_id,
    topic_id,
    lesson_id,
    question_id,
    subject_id,
    question_type_id,
    knowledge_type_ids,
    complexity_level
FROM  lesson_practice_questions as lpq
INNER JOIN chapter_lessons as cl on cl.topic_id = 2 and cl.lesson_id = lpq.lesson_id
INNER JOIN questions as q ON q.question_id = lpq.question_id;

Upvotes: 1

Related Questions