Let's say that I have two tables:

1. lessons with columns: lessonID, name

2. studentlessons with columns: lessonID, age

I am trying to perform this action:

SELECT lessons.*, studentlessons.* FROM studentlessons   
JOIN lessons WHERE studentlessons.lessonID = lessons.lessonID

but the result is a table which have the columns:

lessonID, name, lessonID(1), age

I want to avoid the lessonID(1), so my desired output must be:

lessonID, name, age

I know that I can use this syntax:

SELECT lessons.lessonID, lessons.name, studentlessons.age FROM studentlessons   
JOIN lessons WHERE studentlessons.lessonID = lessons.lessonID

but I can't because of some other reasons.

Is there any purely SQLite syntax that can give me my desired output?

CodePudding user response：

What you want is a NATURAL JOIN:

SELECT * FROM studentlessons NATURAL JOIN lessons 

which returns only one of the columns lessonID.
The tables are joined implicitly on the columns that have the same name(s).

See the demo.