PDA

View Full Version : Joinig tables of SQLite with ODBC



Uwe1966
2006-08-31, 06:16
Hi,

i installed on my WinXP-System the ODBC Drivers for SQLite from http://www.ch-werner.de/sqliteodbc/ version 0.70

I have a problem joining tables because of different fieldtypes in the joinded fields of the tables.
In table "album" is a field "contributor" which is to join to table "contributors" field "id". These fields have different types and can not be joined with ms-access ot crystal-reports.

Does somebody have an idea what to to?

regards,
Uwe

MrStan
2006-08-31, 06:30
Hi,

i installed on my WinXP-System the ODBC Drivers for SQLite from http://www.ch-werner.de/sqliteodbc/ version 0.70

I have a problem joining tables because of different fieldtypes in the joinded fields of the tables.
In table "album" is a field "contributor" which is to join to table "contributors" field "id". These fields have different types and can not be joined with ms-access ot crystal-reports.

Does somebody have an idea what to to?

regards,
Uwe

I am not sure of the usefullness of the field "contributor" in the album table. You see there can be multiple contributors attached to an album so the only reliable way of joining album to contributor is to use the contributor_album table. You will find that the "contributor" field here is of the same type as the "id" in the contributor table. Also the album field identifies the "id" in the album table. It only remains to check that the "role" field is "1" if looking for artist and you can set up a query using the contributor_album and the contributors tables to return all contributors for a given album or visa-versa.
You can do the same for Tracks by using the contributor_track table and if you wish to search for composer set the "role" to "2".

It sounds messy but once you get down to trying it, it's not that difficult.

Hope that makes sense.

Uwe1966
2006-08-31, 07:18
Mr-Stan,

thank you for your help.
I tryed to join the fields as you described it - an it works!
Now i can generate lists of my library to browse the content via paper.

thank you MR-Stan,
Uwe

MrStan
2006-09-02, 10:15
You're very welcome.

Stan