Re: [sqlite] how do I use more than one column as a "combined key"?
I don't undertand what is your question? a) You know to do it in SQL but you don't in SQLite or b) You dont know how to do it in any SQL yaconsult escribió: > SQL newbie here. Sqlite has been a fantastic tool for analyzing, comparing > and correcting lots of account information. But I've hit a roadblock > because I don't know how to treat multiple columns as a kind of combined > key. > > I need to know how to relate two tables > on multiple columns. > > It's been easy when I only had to relate > using a single column. > > The tables are pretty big - 20,000+ entries. > > User Accounts > name > uid > server > login > . > . > . > > Calendar Accounts > server > login > firstname > lastname > . > . > . > > > What I need to be able to do is to check > that the server and login information > in the first table matches one and only > one of the accounts in the second table. > > What I'm having trouble figuring out is > how to use the server and login as > a combined key. > > When I have a single unique value that > I can relate, like a DS ID, I know how > to do that. > > How can I query to find out which user > accounts don't match up with one and > only one calendar accounts? And which > calendar accounts are not associated > with a single user account? > > Thanks for any help you can provide. > Sqlite is the perfect tool for this kind of stuff! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how do I use more than one column as a "combined key"?
yaconsult wrote: >User Accounts > name > uid > server > login > . > . > . > >Calendar Accounts > server > login > firstname > lastname > . > . > . > > > What I need to be able to do is to check > that the server and login information > in the first table matches one and only > one of the accounts in the second table. select * from UserAccounts u where 1 != ( select count(*) from CalendarAccounts c where c.server = u.server and c.login = u.login ); This will return all the "bad" records in UserAccounts. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how do I use more than one column as a "combined key"?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 yaconsult wrote: > What I need to be able to do is to check > that the server and login information > in the first table matches one and only > one of the accounts in the second table. > > What I'm having trouble figuring out is > how to use the server and login as > a combined key. The general process of doing this is called normalization. Wikipedia has an article to start you off: http://en.wikipedia.org/wiki/Database_normalization Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkoJ5l8ACgkQmOOfHg372QRBHQCfTSweDCxgImb44SKG3uGnqi3e CXYAoJc4TDmmdPyRgafrlrteXgLmGrKw =Wjx8 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how do I use more than one column as a "combined key"?
SQL newbie here. Sqlite has been a fantastic tool for analyzing, comparing and correcting lots of account information. But I've hit a roadblock because I don't know how to treat multiple columns as a kind of combined key. I need to know how to relate two tables on multiple columns. It's been easy when I only had to relate using a single column. The tables are pretty big - 20,000+ entries. User Accounts name uid server login . . . Calendar Accounts server login firstname lastname . . . What I need to be able to do is to check that the server and login information in the first table matches one and only one of the accounts in the second table. What I'm having trouble figuring out is how to use the server and login as a combined key. When I have a single unique value that I can relate, like a DS ID, I know how to do that. How can I query to find out which user accounts don't match up with one and only one calendar accounts? And which calendar accounts are not associated with a single user account? Thanks for any help you can provide. Sqlite is the perfect tool for this kind of stuff! -- View this message in context: http://www.nabble.com/how-do-I-use-more-than-one-column-as-a-%22combined-key%22--tp23510319p23510319.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
