Need help limiting a join in Transact-sql

Posted by MsLis on Stack Overflow See other posts from Stack Overflow or by MsLis
Published on 2010-04-15T17:19:27Z Indexed on 2010/04/15 17:23 UTC
Read the original article Hit count: 659

Filed under:
|
|
|
|

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.

© Stack Overflow or respective owner

Related posts about join

Related posts about tsql