yaconsult wrote:
> 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. I'll not go into
> detail about why the data is formed the way it is - it comes from other
> programs.
>
> 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
>
> How can I, for example, select rows of the first table where their server
> and login, combined, are NOT in the second table.
> It's sorta like a composite key. Do I do it with a couple of joins? Could
> I see an example?
>
> 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.
>
> Thanks for any help!
>
Let me apologize up front since I have no SQLite experience but I'll
give you some samples that work under SQL Server. There are going to be
some syntactic differences moving to SQLite but I'll leave that as an
exercise for you! :-)
Here is one *flawed* possibility. I assume here that table_1 is some
sort of user table which defines a login and a server that could be
logged into while Table_2 defines actual logins to a server with a
particular login. First you match the tables up on the server columns
being identical and then select the records that do not have a login in
the second table. This subquery by itself would pick up all records
where the server had been added to a login but would miss records if the
server had never been logged into by anyone. (Since there would be no
records in the table which records specific logins, there would be no
matches. Therefore we add the second subquery where we match them on the
login and then grab servers that had never been logged into. Problem is
if we have a login with a server which has never been logged into and a
user who has never logged into any server we'd still miss them. It is a
very quick query though and if you know there will never be any servers
that have not been logged into you can drop the second half of the query.
SELECT DISTINCT T1.Server, T1.Login
FROM TABLE_1 T1,
TABLE_2 T2
WHERE (
T1.Server = T2.Server
AND ( T1.Login
NOT IN(
SELECT DISTINCT Login
FROM TABLE_2
WHERE TABLE_2.Server = T1.Server
)
)
)
OR ( T1.Login = T2.Login
AND ( T1.Server
NOT IN( SELECT DISTINCT Server
FROM TABLE_2
WHERE TABLE_2.Login = T1.Login
)
)
)
Now, another query that would do the job would be. We still don't like
it since it uses too many reqources. Here we query or manufacture a row
for every row in the user table. We then check for rows that have been
manufactured and grab them.
SELECT DISTINCT T1.Server User_Table_Server, T1.Login
User_Table_Login, T2.Server Login_Table_Server, T2.Login Login_Login
FROM TABLE_1 T1
LEFT OUTER JOIN TABLE_2 T2
ON( T1.Server = T2.Server
AND
T1.Login = T2.Login )
WHERE T2.Server IS NULL
Lastly, the query you ACTUALLY want is:
SELECT *
FROM TABLE_1 T1
WHERE NOT EXISTS (
SELECT *
FROM TABLE_2 T2
WHERE T1.Server = T2.Server
AND T1.Login = T2.Login
)
Regards,
Greg
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
