CODEWITHSUNDEEP
sql-server
MaximeBTD
MaximeBTD

Reputation: 49

Get only first result outer join SQL Server

I got two tables, one with a list of customers, and another with a list of contract. Each customer can have multiple contracts. I want to get all customer's Nos, Names, and if they have a contract or not. This is my request :

SELECT c.[no_], 
       c.[name], 
       s.[contract no_] 
FROM   [contact] AS c 
       LEFT OUTER JOIN [service contract header] AS s 
                    ON c.[no_] LIKE s.[contact no_] 
                       AND s.[serv_ contract acc_ gr_ code] LIKE 'SAV' 
ORDER  BY s.[contract no_]

The result i'm getting is a list of all customers, with their contracts, when they have one, but if a customer has 2 contracts, it appears two times in the result. Once with contract 1 and with contract 2. I'd like to have only one occurence of each customer. Knowing if he has contract 1 or 2 doesn't matter, I just want to know if he has a contract or not. Anything, kinda stuck right now :/ And can't figure out what to search on the web to find an answer

Upvotes: 1

Views: 33

Answers (2)

Derrick Moeller
Derrick Moeller

Reputation: 4960

You can use GROUP BY to return an aggregate, COUNT for example can give you the quantity of contracts.

SELECT c.[No_], c.[Name], COUNT(s.[Contact No_]) AS Contracts
FROM [Contact] AS c 
LEFT JOIN [Service Contract Header] AS s ON c.[No_] LIKE s.[Contact No_] AND s.[Serv_ Contract Acc_ Gr_ Code] LIKE 'SAV' 
GROUP BY c.[No_], c.[Name]

You can also place a filter on the GROUP BY, it's known as HAVING. This will return all the customers with atleast one contract.

SELECT c.[No_], c.[Name]
FROM [Contact] AS c 
LEFT JOIN [Service Contract Header] AS s ON c.[No_] LIKE s.[Contact No_] AND s.[Serv_ Contract Acc_ Gr_ Code] LIKE 'SAV' 
GROUP BY c.[No_], c.[Name]
HAVING COUNT(s.[Contact No_]) >= 1

Upvotes: 2

Ruslan K.
Ruslan K.

Reputation: 1981

Another way is APPLY clause:

SELECT c.[No_], c.[Name], a.cnt as ContactsCount
    FROM [Contact] AS c 
    OUTER APPLY (SELECT count(*) cnt FROM [Service Contract Header] s WHERE c.[No_] = s.[Contact No_] AND s.[Serv_ Contract Acc_ Gr_ Code] = 'SAV') a
    ORDER BY c.[No_]

Upvotes: 0

Related Questions