Hi ya (well that's the only name you give ;-) )
> SQL newbie here. I have data in a couple of tables that I need to
> relate.
> But I don't know how to relate on more than one column.
> For example, let's say there are a couple of tables:
>
> server login name last_login email ...
>
> and we have a second table that contains columns for server and login:
>
> server login
It would help to see the actual schema. I'm guessing that your schema
is something like:
create table Servers
(
ID integer primary key
, Server text
, Login text
, Name text
, Last_login datetime
, Email text
)
;
create table Attempts
(
ID integer primary key
, Server text
, Login text
)
;
> How can I, for example, select rows of the first table where their
> server and login, combined, are NOT in the second table.
Something like this:
select * from Servers where not exists (select 1 from Attempts where
Servers.Server = Attempts.Server and Servers.Login = Attempts.Login);
or:
select Server, Login from Servers
except
select Server, Login from Attempts
;
> If it were only a single column I could use a "not in" subselect,
> but I'm not sure how to do it when it involves more than one column.
Whether it's matching on one or multiple columns, it's best not to use
"not in" or "in", but instead use "not exists (select 1..." or "not
exists (select 1..." as above. That way, the database engine only has
to find the first match (or non match), rather than scan the whole
table redundantly.
HTH,
Tom
BareFeet
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
