Need help limiting a join in Transact-sql
- by MsLis
I'm somewhat new to SQL and need help with query syntax.
My issue involves 2 tables within a larger multi-table join under Transact-SQL (MS SQL Server 2000 Query Analyzer)
I have ACCOUNTS and LOGINS, which are joined on 2 fields: Site & Subset.
Both tables may have multiple rows for each Site/Subset combination.
ACCOUNTS: | LOGINS:
SITE SUBSET FIELD FIELD FIELD | SITE SUBSET USERID PASSWD
alpha bravo blah blah blah | alpha bravo foo bar
alpha charlie blah blah blah | alpha bravo bar foo
alpha charlie bleh bleh blue | alpha charlie id ego
delta bravo blah blah blah | delta bravo john welcome
delta foxtrot blah blah blah | delta bravo jane welcome
| delta bravo ken welcome
| delta bravo barbara welcome
I want to select all rows in ACCOUNTS which have LOGIN entries, but only 1 login per account.
DESIRED RESULT:
SITE SUBSET FIELD FIELD FIELD USERID PASSWD
alpha bravo blah blah blah foo bar
alpha charlie blah blah blah id ego
alpha charlie bleh bleh blue id ego
delta bravo blah blah blah jane welcome
I don't really care which row from the login table I get, but the UserID and Password have to correspond. [Don't return invalid combinations like foo/foo or bar/bar] MS Access has a handy FIRST function, which can do this, but I haven't found an equivalent in TSQL.
Also, if it makes a difference, other tables are joined to ACCOUNTS, but this is the only use of LOGINS in the structure.
Thank you very much for any assistance.