CODEWITHSUNDEEP
sqlite
bullet
bullet

Reputation: 75

Sqlite: merge two columns among that returned from a select

I need help with the following scenario.

I have two tables, the first (TableA) is a list of people and the second (TableB) a list of cities.

TableA has a column called city_id that refers to an id in TableB.

I'm using the following query:

SELECT TableA.name,TableB.city,TableB.state FROM TableA LEFT JOIN TableB ON TableA.city_id=TableB.id;

the resulting query has three columns: name,city,state.

Is it possible to get just 2 columns: name,city+state?

I tried the following without luck:

SELECT TableA.name,(`TableB.city` || ' ' || `TableB.state`) FROM TableA LEFT JOIN TableB ON TableA.city_id=TableB.id;

Thanks for any help.

Upvotes: 0

Views: 39

Answers (1)

MikeT
MikeT

Reputation: 56953

Your issue is that by enclosing TableB.city in ` you are saying that the column is called TableB.city (i.e. the TableB part becomes part of the column name) and that column will not be found (likewise for TableB.state).

So the fix is to not enclose the table and column in `'s.

The following should work :-

SELECT TABLEA.name,TableB.city||' '||TableB.state FROM TableA JOIN TableB ON TableA.city_id = TableB.id;

Full example :-

DROP TABLE IF EXISTS TableA;
DROP TABLE If EXISTS TableB;
CREATE TABLE IF NOT EXISTS TableA (id INTEGER PRIMARY KEY, name TEXT, city_id INTEGER);
CREATE TABLE IF NOT EXISTS TableB (id INTEGER PRIMARY KEY, city TEXT, state TEXT);
INSERT INTO TableB (city,state) VALUES ('Miami','Florida'),('Dallas','Texas');
INSERT INTO TableA (name,city_id) VALUES ('Fred',2),('Mary',1),('Tom',1),('Susan',2);
SELECT TABLEA.name,TableB.city||' '||TableB.state FROM TableA JOIN TableB ON TableA.city_id = TableB.id;

Results in :-

enter image description here

Upvotes: 1

Related Questions