Using UNION to combine same SQL Tables and searching for a specific word return duplicate rows

Question

I want to do the same[ on logic ] as this question -> SQL/mysql - Select distinct/UNIQUE but return all columns?

Using SELECT DISTINCT PATH FROM... instead of SELECT * FROM doesn't returns duplicates [ UNION works ] but it does return only one table column [PATH] , i want all the columns of the tables not only the Path column .

**Basically what i want to do is sql select multiple columns with only one distinct column ** the column PATH

Here is the SQLITE3 db file ->https://github.com/goxr3plus/Tester

Example sql statements for the db :

SELECT * FROM '_44057' UNION SELECT * FROM '_76468' UNION SELECT * FROM '_25912' UNION SELECT * FROM '_12603'  WHERE PATH LIKE '%e%';

SELECT * FROM '_44057' UNION SELECT * FROM '_76468' UNION SELECT * FROM '_25912' UNION SELECT * FROM '_12603'  WHERE PATH LIKE '%e%' GROUP BY PATH; 

---

I am using SQLITE and i have 4 same tables with the same data and colums . So the tables contains the columns (PATH->this is primary key,STARS,...).

Actually here is the Table create statement :

"CREATE TABLE '" + dataBaseTableName + "'" 
                 + "(PATH       TEXT    PRIMARY KEY   NOT NULL ,"
                 + "STARS       DOUBLE     NOT NULL,"
                 + "TIMESPLAYED  INT     NOT NULL,"
                 + "DATE        TEXT    NOT NULL,"
                 + "HOUR        TEXT    NOT NULL)");

What i want to do is search for those items-songs that contain the specific word , for example a. So i am using UNION to collect all the tables data and finally a WHERE STATEMENT with a LIKE , finally i limit my results to 50. I also don't want dublicates but for some reason i am getting duplicate rows ...

• The problems:

1)Dublicate rows are being returned

2)When tables contains a lot of elements it is getting slow

• Small Explanation for code below:

1. Where you can see '%" + word + "%' , word is a String for example a
2. The below SQL Statement is dynamically generated , i mean that i can have more that 4 tables and each table can contain thousands of songs [ That makes the query very slow sometimes )

---

Simple SQL code:

SELECT * FROM '_44057'
 UNION 
SELECT * FROM '_76468' 
UNION 
SELECT * FROM '_25912' 
UNION 
SELECT * FROM '_12603' 
WHERE PATH LIKE '%" + word + "%' LIMIT 50

Finally:

Any recommendation , i mean anything from LIKE to Selector UNION replacing is appreciated a lot cause i am new to this :).

Image from Duplicate rows:

Answer 1

This is the SQLite part of the solution, please wrap into java yourself:

SELECT * FROM (
  SELECT * FROM _12603 UNION ALL 
  SELECT * FROM _25912 UNION ALL 
  SELECT * FROM _44057 UNION ALL 
  SELECT * FROM _76468
) 
WHERE 
REPLACE(path, rtrim(path, replace(path, '\', '')), '') -- only filename
LIKE '%e%' GROUP BY path;

There is no explicit control on which line of identical paths is used.
OP has stated (in chat) that any one is fine, no need for special selection.
So just using group by is good enough.
However, comments are welcome on whether this (using group by without aggregate function) is clean/portable/reliable.

I tested with OPs mcve and
SQLite 3.18.0 2017-03-28 18:48:43 424a0d380332858ee55bdebc4af3789f74e70a2b3ba1cf29d84b9b4bcf3e2e37

OP tried on https://sqliteonline.com and was satisfied.

Just to mention: in the chat OP spotted an unneeded workaround (for some silliness of mine), which improved efficiency.

Using totally amazing answer by ungalcrys to
How to get the last index of a substring in SQLite?