Merging Two Queries

• Ashwin9

  SSC-Addicted

  Points: 477

  More actions

  July 23, 2011 at 3:53 am

  #241986

  Hi friends,

  I want to merge two queries.

  I've table name "UsersTable" where I'm retrieving duplicate username. For this I wrote ...

  SELECT UserName, COUNT(*) TotalCount From UsersTable

  GROUP BY UserName

  HAVING COUNT(*) > 1

  ORDER BY COUNT(*) DESC

  Result

  --------------------------

  UserNameTotalCount

  --------------------------

  sunita 34

  shubham 31

  sunil 30

  anita 30

  Now I want to display Ids of each UserName which is stored in table named "ContactsTable".

  For this I used JOINS :

  SELECT * FROM ContactsTable CT

  INNER JOIN UsersTable UT ON CT.ContactId = UT.UserContactId

  AND UT.UserName = 'sunita'

  But this will return only one contact details whose name is "sunita".

  So for this I want to merge above two queries so that I can get details of all UserName.

  Is there any direct way to do this. If possible not by stored procedure.

  Thanks in Adv.

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  July 23, 2011 at 12:57 pm

  #1357445

  If possible not by stored procedure.

  Why not? Hopefully, you're not making the mistake of using embedded code in a GUI or SSIS or...

  I'm not sure why you'd do such a thing by user name but whatever... here's one of a half dozen different ways to accomplish your given task.

  SELECT *

  FROM ContactsTable CT

  INNER JOIN UsersTable UT ON CT.ContactId = UT.UserContactId

  AND UT.UserName IN

  ( --=== Find duplicate user names

  SELECT UserName

  FROM dbo.UsersTable

  GROUP BY UserName

  HAVING COUNT(*) > 1

  ;

  )

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)