Need help limiting connection in Transact-sql

Question

I'm a little new to SQL and need help with the query syntax.

My problem includes 2 tables as part of a larger join of several tables in Transact-SQL (MS SQL Server 2000 Query Analyzer)

I have ACCOUNTS and LOGINS, which are combined in 2 fields: a site and a subset. Both tables can have multiple rows for each combination of sites / subsets.

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 that have LOGIN entries, but only 1 for each 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

, , . [ , foo/foo bar/bar] MS Access FIRST, , TSQL.

, , ACCOUNTS, LOGINS .

.

: USERID/PASSWD LOGIN.

Answer 1

.

, USERID PASSWD select MAX .

:

SELECT ac.SITE, ac.SUBSET, MAX('user='lo.USERID+'&password='+lo.PASSWD) as IdPwd
FROM ACCOUNTS ac, LOGIN lo
WHERE ac.SITE = lo.SITE
AND ac.SUBSET = lo.SUBSET
GROUP BY ac.SITE, ac.SUBSET

Answer 2

autonumber. .

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

: , autonumber, , , .

: temp table, :

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

Answer 3

,

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 internal SELECT receives only one input for each account, and then joins them back to the accounts to get the final result.

Answer 4

This may work as long as the USERID / PASSWD commands are unique to each SITE / SUBSET combination.

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 works 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;

(they didn’t actually try to run them, so a syntax error might have crept somewhere)