Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Need to combine 2 queries

I have 2 queries that I need to merge into 1 statement. I am trying to create a list of those who have successfully logged in to the system in the last (14) days.

The first query returns only the Users.LoginSuccess column. The second returns the other information I need to bind to that so that the query outputs all of the following rows:

Users.LoginSuccess + ProviderGroup.Company, ProviderGroup.NameFirst, ProviderGroup.NameLast, ProviderGroup.GroupSize, ProviderGroup.City, ProviderGroup.State,
ProviderGroup.ReferrerName, ProviderGroup.SignOnDate, ProviderGroup.Status

1. The following query works to pull LoginSuccess values:

SELECT Users.LoginSuccess
FROM Users
WHERE Users.UserTable='Provider'
AND Users.UserTableID
IN
(SELECT    Provider.ProviderID
FROM  Provider
WHERE     Provider.ProviderGroupID

IN (SELECT ProviderGroup.ProviderGroupID
      FROM      ProviderGroup
      WHERE     (ProviderGroup.SignOnDate > Getdate() - 14)
        AND       ProviderGroup.Status = 1))

2. The following query gets the other info.  I need to merge them.

SELECT    ProviderGroup.Company, ProviderGroup.NameFirst, ProviderGroup.NameLast, ProviderGroup.GroupSize, ProviderGroup.City, ProviderGroup.State,
ProviderGroup.ReferrerName, ProviderGroup.SignOnDate, ProviderGroup.Status
FROM       ProviderGroup
WHERE     (SignOnDate > Getdate() - 14) AND (Status = 1)
0
mcsolas
Asked:
mcsolas
1 Solution
 
rafranciscoCommented:
Try this:

SELECT Users.LoginSuccess, ProviderGroup.Company, ProviderGroup.NameFirst, ProviderGroup.NameLast, ProviderGroup.GroupSize, ProviderGroup.City, ProviderGroup.State,
ProviderGroup.ReferrerName, ProviderGroup.SignOnDate, ProviderGroup.Status
FROM Users
INNER JOIN Provider
     ON Users.UserTableID = Provider.ProviderID
INNER JOIN ProviderGroup
     ON Provider.ProviderGroupID = ProviderGroup.ProviderGroupID AND
          ProviderGroup.Status = 1 AND
          ProviderGroup.SignOnDate > DATEADD(DD, -14, GETDATE())
WHERE Users.UserTable='Provider'
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now