I've got 2 databases with 3 tables in a single SQLite3 database connection. I create a temporary table on these tables/databases to support better and quicker searching/filtering.
Here is an example. The real tables are much bigger.
1st table:
rowid(unique)|firstname|lastname
2nd table:
id(from 1st table)|phonenumber
3rd table:
id(from 1st table)|emailaddress
I've got 2 (even more in the future) databases of this kind. To create a temporary table I do the following: (There is one database connection and a separate database "pb" attached.)
CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM pb.name_table
LEFT JOIN pb.phone_table ON (pb.name_table.id=pb.phone_table.id)
LEFT JOIN pb.email_table ON (pb.name_table.id=pb.email_table.id)
UNION SELECT * FROM name_table
LEFT JOIN phone_table ON (name_table.id=phone_table.id)
LEFT JOIN email_table ON (name_table.id=email_table.id);
This generates a flat table like this:
firstname|lastname|phonenumber|emailaddress
But (and this is finally the question) if a contact has (e.g.:) 3 phone numbers and 3 email addresses, I get 3*3 entries in the result table. The result table contains a combination of all phone number - email address combinations.
Is there a way to create a flat table with 3 entries only?
Current situation:
firstname - lastname - email1 - phone1
firstname - lastname - email1 - phone2
firstname - lastname - email1 - phone3
firstname - lastname - email2 - phone1
firstname - lastname - email2 - phone2
firstname - lastname - email2 - phone3
firstname - lastname - email3 - phone1
firstname - lastname - email3 - phone2
firstname - lastname - email3 - phone3
Optimal solution (maybe in a different order):
firstname - lastname - email1 - phone1
firstname - lastname - email2 - phone2
firstname - lastname - email3 - phone3
Okay, it took some time, but I got rid of the temporary table. I programmed a "QueryBuilder" which generates queries for given result/filter/sort/...
It's quiet complex, but - well - you have to do this for performance. :-)
Thanks!