SQLite view column name, specifically in SQLiteStudio

Question

I have two tables in a SQLite3 database, table a has columns id (primary key) and name, table b has columns parent (foreign key into a) and name as well.

I create a view for the join:

CREATE VIEW a_b AS
    SELECT *
      FROM a
           JOIN
           b ON b.parent = a.id;

I can do this in SQLiteStudio and the view works. I can now run a query like:

SELECT * FROM a_b

And get the expected results. However, since both a and b have a column called name, the second column is renamed to name:1 so if I want to select only that column, I need something like:

SELECT `name:1` FROM a_b

This works, but I was wondering if there's a better way to rename the columns in the view without renaming literally every column with:

CREATE VIEW a_b AS
    SELECT a.id as a_name, a.name as a_name, b.parent as b_parent, b.name as b_name
      FROM a
           JOIN
           b ON b_parent = a_id;

This works, but is clearly impractical for larger tables and also requires updating the view every time something changes about a or b. I know I can refer to the fields of the separate tables like like a.*, so I was looking for something like:

CREATE VIEW a_b AS
    SELECT a.* AS a_*, b.* AS b_*
      FROM a
           JOIN
           b ON b.parent = a.id;

This is clearly not code that works, but I hope it illustrates the requirement.

Is there a working, valid way to achieve this?

The best I was able to get was something like this:

CREATE VIEW a_b AS
    SELECT *, b.name as b_name
      FROM a
           JOIN
           b ON b.parent = a.id;

This at least defines an alias for b.name, but it also duplicates the column in the view.

Note: I'd be happy with a solution that only works in SQLiteStudio, but I suspect the solution is going to be a SQLite one, not an application-specific one.

SQLite view column name, specifically in SQLiteStudio

0 Answers0