On 2014/05/21 18:00, Humblebee wrote:
Thank you for your replies.
I'm sorry for not being super clear about the problem:
Note: The parId belongs to another table not shown here.
TeamTable
+---------------------------------+
| id | parId | personIds |
+---------------------------------+
| 1 | 4 | 1,5,2,3,4 |
| 2 | 5 | 2,6,3,5,1 |
+----------------------------------|
PersonTable
+------------------+
| id | name |
+------------------+
| 4 | 4 |
| 5 | 5 |
+---------+-------+
@Simon, thank you for showing me a better way to setup the tables,
your way makes much more sense, only problem is that in this
situation, the tables have already been defined and made by someone
else so I cannot change it. I'm a bit stuck with the way it is.
There is no other way sadly, you have to make the data behave. You have some
options though depending on some other things like:
1) We know you cannot change the current tables but can you change how they are
created?
This might mean you can change the creating or inserting scripts and then just convert the existing tables to the correct format,
which we can tell you easy ways to achieve.
2) You cannot change how the tables are created, but you can add more tables?
This means we can simply add the tables in the way we like them and only need a trigger or such to decompose the data into our newer
tables when the originator inserts them.
3) You cannot add new tables or change the data entry process at all.. you
/HAVE/ to just use a query...
Well in that case, we've had the deravelling of csv data on this list before, and I did make some CTE (which I will repost below) to
fix it, but it is slow and you will need to be using a version of SQLite that supports CTE (3.8.4+) - it's ugly but will do the job.
4) If you control the C code that actually executes the query in your own software.. the very best solution would be reading the
next value and decomposing the csv INSIDE your own code, and then adding it to a next query that gets the final results. Problem is
the Query values need to be known at *Prepare* time before any steps, so it will have to be individual queries for each extraction -
so may well be as slow as the CTE solution.
You really cannot however expect to have anything speedy when your tables are not in 1NF format as Simon suggested and you should
strongly recommend this alteration be made to the powers that be.
Anyway, the next bit of CTE (copy-lifted from previous posts) will extract you the csv values, it's contains both the working code
and an example table (maybe try to run the example first) so you should translate it a bit to suit your needs, if you have any
difficulty, please ask (and it needs recent version SQlite as I mentioned...) Best of luck!
---------------------------------------------------------
CREATE TABLE tmpcsv (
ID INTEGER PRIMARY KEY,
colA TEXT,
colCSV TEXT
);
INSERT INTO tmpcsv (colA, colCSV) VALUES
('foo', '4,66,51,3009,2,678'),
('bar', 'Sputnik,Discovery'),
('baz', '101,I-95,104');
WITH csvrec(i,l,c,r) AS (
SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv
UNION ALL
SELECT i,
instr(c,',') AS vLen,
substr(c,instr(c,',')+1) AS vRem,
substr(c,1,instr(c,',')-1) AS vCSV
FROM csvrec
WHERE vLen>0
)
SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt
WHERE t.ID=rt.i AND rt.r<>''
ORDER BY t.ID
LIMIT 100
Results:
ID colA r
--- ---- ---
1 foo 4
1 foo 66
1 foo 51
1 foo 3009
1 foo 2
1 foo 678
2 bar Sputnik
2 bar Discovery
3 baz 101
3 baz I-95
3 baz 104
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
