Need help limiting a join in Transact-sql

Question

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.

Quick added info: USERID/PASSWD combinations are unique across the entire LOGIN table.

Answer 1

Here is a query that may work

Select accounts.Site, accounts.Subset, Logins.UserId, Logins.Passwd
From Accounts 
Inner Join
(
    select top 1 Site, Subset, UserId, Passwd
    From Logins logn
    Where Exists
    (
        Select 1
        From Accounts Acc
        where 1=1
        and Acc.Site = Logn.Site
        and Acc.Subset = Logn.Subset
    )
) as OneLoginPerSite
On  Accounts.Site = OneLoginPerSite.Site
and Accounts.Subset = OneLoginPerSite.Subset

The Inner SELECT gets you only one Login per Account and then you join this back to the Accounts to get your final result.

Answer 2

This may work assuming that USERID/PASSWD combos are unique per SITE/SUBSET combo

select a.*, l1.USERID, l1.PASSWD
from ACCOUNTS a
    join LOGINS l1 on a.SITE = l1.SITE and a.SUBSET = l1.SUBSET
where not exists (select * from LOGINS l2 where l1.SITE = l2.SITE and l1.SUBSET = l2.SUBSET and l2.USERID < l1.USERID and l2.PASSWD < l1.PASSWD);

also try this to see if it runs faster

select a.*, l1.USERID, l1.PASSWD
from ACCOUNTS a
    join LOGINS l1 on a.SITE = l1.SITE and a.SUBSET = l1.SUBSET
    left outer join LOGINS l2 on l1.SITE = l2.SITE and l1.SUBSET = l2.SUBSET and l2.USERID < l1.USERID and l2.PASSWD < l1.PASSWD
where l2.SITE is null;

(didn't actually try running these, so a syntax error may have crept in somewhere)

Answer 3

It would be easier with an autonumber field. You don't really have a good primary key for this table.

Select *
From
(
   Select max(id) as MaxID, Site, Subset
   from logins 
   group by site, subset
) UniqueLogins
INNER JOIN logins on UniqueLogins.MaxID = Logins.ID
INNER JOIN Accounts ON logins.site = accounts.site and logins.subset = accounts.subset

Edit: If you can't change schema, you can always dump the records into a temp table with an autonumber, but it may be inefficient if this is being run often, but it will work.

Edit again: If you went the temp table approach, the code for that is:

create table #tmp(
    ID int identity(1,1) primary key
    ,Site <data type>
    ,Subset <data type>
    ,userid <data type>
    ,password <data type>
)

Insert into #tmp(Site 
    ,Subset 
    ,userid 
    ,password )
Select * From logins
--where ???

Select *
From
)
    Select #tmp.* From(
       Select max(id) as MaxID, Site, Subset
       from #tmp 
       group by site, subset
    ) UniqueSites
    INNER JOIN #tmp on #tmp.ID = UniqueSites.MaxID
) UniqueLogins
INNER JOIN Accounts ON UniqueLogins.Site = Accounts.Site and UniqueLogins.Subset = Accounts.Subset

--do whatever else
drop table #tmp