CODEWITHSUNDEEP
sqlsql-servert-sql
Alan Thomas
Alan Thomas

Reputation: 47

return rows only if a certain conditions match up with another table

I want an SQL query that returns a list of users, but only if they have a certain link and not another.

So I have 2 tables: TABLE 1- USERS

USER     AGREEMENTID
--------------------
USER1    1
USER2    3
USER1    3
USER3    3
USER3    4
USER4    1

TABLE 2- AGREEMENTS

ID   PRODUCT
-------------------------
1    ServiceAgreement
2    ServiceAgreement
3    AggregationAgreement
4    AggregationAgreement
5    ServiceAgreement

So for my results on the above example i only expect USER2 and USER3 to be returned.
USER 1 has two links but one of those is ServiceAgreement so it shouldn't be returned in the results.
USER 2 has link to just 1 aggregation agreement so this should be returned in the results.
USER 3 has two links but both are to AggregationAgreement so this should be returned in the results.
USER 4 has one link but it's to a ServiceAgreement so this should no be returned in the results.

Hope that all makes sense, as always appreciate any help.

Upvotes: 1

Views: 428

Answers (4)

cloudsafe
cloudsafe

Reputation: 2504

Using EXCEPT:

declare @Users table ([User] nvarchar(10), [AGREEMENTID] int)
insert into @Users values 
  ('USER1', 1)
, ('USER2', 3)
, ('USER1', 3)
, ('USER3', 3)
, ('USER3', 4)
, ('USER4', 1)

declare @Agreements table ([ID] int, [Product] nvarchar(100))
insert into @Agreements values
  (1, 'ServiceAgreement')
, (2, 'ServiceAgreement')
, (3, 'AggregationAgreement')
, (4, 'AggregationAgreement')
, (5, 'ServiceAgreement')

select u.[User] from @Users u inner join @Agreements a on u.AGREEMENTID = a.ID and a.Product = 'AggregationAgreement'
except
select u.[User] from @Users u inner join @Agreements a on u.AGREEMENTID = a.ID and a.Product = 'ServiceAgreement'

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can also use a query like below

select user
from 
(
select 
  user=u.[user], 
  weight= case a.product ='ServiceAgreement' then -1 else 0 end -- negative weight for undesirable agreement
from
  users u join agreements a 
    on u.AgreementId=a.AgreementId
    and a.product in ('ServiceAgreement','AggregationAgreement') -- we only have two agreements of interest here
)t
group by [user]
having sum(weight)=0 -- should not be negative

Upvotes: 1

Steven
Steven

Reputation: 911

This would return the users with the agreements, excluding ones that have a ServiceAgreement linked.

SELECT USERS.[UserName]
     , AGREEMENTS.[AgreementId]
     , AGREEMENTS.[Product]
FROM   USERS
       INNER JOIN AGREEMENTS
           ON AGREEMENTS.[AgreementId] = USERS.[AgreementId]
WHERE  USERS.[UserName] NOT IN
           (
               SELECT USERS.[UserName]
               FROM   USERS
                      INNER JOIN AGREEMENTS
                          ON AGREEMENTS.[AgreementId] = USERS.[AgreementId]
               WHERE  AGREEMENTS.[Product] = 'ServiceAgreement'
           )

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

Try this:

select * 
from users u
where exists 
(
    select 1 
    from agreements a 
    where u.agreementid=a.id and a.product='AggregationAgreement'
)
and not exists
(
    select 1 
    from agreements a2 
    where u.agreementid=a2.id and a2.product<>'AggregationAgreement'
)

Upvotes: 1

Related Questions