On 17-11-10 10:32, Arigead wrote: > Hi all, > I've started to use an existing Database with C source code. As I'm new > to > all this I can't moan about database design but I'm sure that Key Value pairs > in a Database table suits modern languages like Python down to the ground. It > ain't suiting me though ;-) > > I'm really struggling to find a solution to my problems so I though I might > try > here. There are two database tables that I'm interested which keep track of > contacts. > > One table "contacts" simply keeps contact_id which is unique > > A second table "contact_name" keeps track of key value pairs for the contacts: > > CREATE TABLE contacts_name > (contacts_name_id INTEGER PRIMARY KEY,contacts_id > REFERENCES contacts(contacts_id), > field_name TEXT, > value TEXT NOT NULL); > CREATE INDEX contacts_name_contacts_id ON contacts_name(contacts_id); > > > So in Contacts table I might have a few id's > 1 > 2 > > And in contacts_name I might have a few key value pairs: > > ID field_name value > 1 Name Tom > 1 Surname Jones > 2 Name Fred > 2 Surname Flintstone > > I didn't design this system and it don't seem ideal to me, coming from C, but > I'll have to get on with it. I decided that to make things simpler for my C I > could create a temporary table and populate it with contact_id, name and > Surname which is all that I'm interested in. > > So I created a new table with: > > create temporary table if not exists contacts_tmp > (contacts_id REFERENCES contacts(contacts_id), > Name TEXT, Surname TEXT) > > That's a database table I could work with ;-) Now I have to populate it with > the data from the existing contacts_name table so I get the contacts_id and > Name inserted with: > > INSERT INTO contacts_tmp (contacts_id, name) > SELECT contacts_id, value FROM contacts_name > WHERE field_name="Name" > > > The above statement works insofar as it populate id and name but I can't get > surname into my table. I've tried to write a second insert statement to pull > out the Surname tag from contacts_name but I just can't get it. I'm trying > something along the lines of: > > INSERT INTO contacts_tmp (Surname) > SELECT contacts_name.value FROM contacts_name where > contacts_name.field_name="Surname" > JOIN contacts_name ON contacts_tmp.contacts_id = contacts_name.contacts_id > > Appologies for the long first post but I can't find a solution. Any advice, > apart from redesigning the old tables would be greatefully received. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users select c.contacts_id, n.value as Name, s.value as Surname from contacts c left join contacts_name n on c.contacts_id=n.contacts_id and n.field_name='Name' left join contacts_name s on c.contacts_id=s.contacts_id and s.field_name='Surname'
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
