On 9 Sep 2009, at 5:15pm, Stef Mientki wrote: > The situation is like this (simplified): > > I've a table with persons: name, address, phone, etc. > I've another table with vehicles: car_or_bike, brand, type, build- > year, etc. > The 2 tables are bounded by bind table: person_ID, vehicle_ID
Well you cannot have one vehicle owned by more than one person. So you do not need your 'bind' table at all. You just need to add a column to the vehicle table which contains the person_ID. > I know that every person has 1 car and 1 bike Do you mean exactly one car ? Or up to but no more than one car ? Or at least one car ? > I want to show up a table, with 3 columns: > persons.name vehicle.brand[car] > vehicle.brand[bike] > > > I can get the result in the following way: > > 1. create view Cars: persons join vehicle, where car_or_bike = car > 2. create view Bikes: persons join vehicle, where car_or_bike = bike > (and change the relevant colum names) > 3. join the above view 1 and 2 > > The first problem is that the above approach is quiet slow. > Both starting tables (persons and vehicles are also views), > and maybe it would be faster to create (temporary) tables ? > > The second problem is that in the real situation > I'm not converting 1 column to 2 columns, > but it can be as large as 1 column to 10 columns. > > btw I'm running SQLite from Python, so maybe I should do some data > handling outside SQL (in Python) ? Yes, you are trying to get SQL to do your programming work for you. Just do two SELECT commands in Python: one to find the car for the person, and another to find the bike for the person. No need to create any temporary tables, create any views, do any joins, or keep a huge table with all the results in. Much smaller and faster. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
