androidsqlitejoinandroid-sqlitenatural-join

SQLite INNER JOIN - How to output only one of the common columns the two tables have

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?

Solution

  • 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.