CODEWITHSUNDEEP
sqlsql-server
Damien
Damien

Reputation: 4319

Returning DISTINCT rows in sql query

I have two tables. One is clients, that has a clientID and entity, the other is LogOnLink which has a clientID and LogonID. The LogOnLink table can the same clientID entered into it multiple times. So i'm trying to run a query where I do a search for an Entity Joining the LogonLink table. The problem i'm having is that if an entity is in the logonLink table 20 times, i get the name returned twenty times. How do i make it so that it only shows me one instance of an entity. Keep in mind that there can be similar entity names so If i have "Company 1" and "Company 2" in the db, i need to show both companies if someone types in companies. I tried doing DISTINCT but it doesn't distinct it by clientID in the LogOnLink table. Here's what i have so far.

SELECT DISTINCT ll.logonID, entity 
FROM clients c 
INNER JOIN LogOnsLink ll ON ll.clientID = c.clientID 
WHERE c.entity LIKE '%Com%' 
ORDER BY entity

Clients Table
------------------------------------
clientID    entity
2             Company A
8             Company B
43            Company C

LogOnLinks Table
------------------------------------
LogonLinkID    clientID    LogonID
4                  2           3
5                  2           7
21                 8           20
6                  2           9
3                  8           10
45                 43          3

Upvotes: 0

Views: 80

Answers (2)

juliovr
juliovr

Reputation: 48

Try this

SELECT ll.logonID, DISTINCT(entity) 
FROM Clients c INNER JOIN LogOnsLink ll 
ON ll.clientID=c.clientID 
WHERE c.entity LIKE '%Com%' 
ORDER BY entity

Upvotes: 0

Spock
Spock

Reputation: 4900

You'll need to perform some kind of aggregate on the data from LogOnsLink or you'll always get 20 rows...

Try this

SELECT entity, COUNT(ll.logonID) AS MaxlogonIDs
FROM clients c 
    INNER JOIN LogOnsLink ll ON ll.clientID=c.clientID 
WHERE c.entity LIKE '%Com%' 
GROUP BY entity
ORDER BY entity

or

SELECT DISTINCT entity
FROM clients c 
    INNER JOIN LogOnsLink ll ON ll.clientID=c.clientID 
WHERE c.entity LIKE '%Com%' 
ORDER BY entity

Upvotes: 2

Related Questions