CODEWITHSUNDEEP
mysqlsqljoin
rgbflawed
rgbflawed

Reputation: 2137

Three Tables, Two Joins, Only One Table Needed For Results

I've got three tables: Clients, Services, and Client Locations. I'm running a query that needs to return the locations of clients that received a certain service. So using the second table in the SELECT and the third table in the WHERE. I'm using two LEFT JOINs and getting my results repeated in an undesirable way.

Here are simplified versions of the three tables...

Clients (clients)

id_client | clientName
----------------------
1         | Abby
2         | Betty
3         | Cathy

Client Services (services) Used only in the WHERE statement

id_client | date      | serviceType
-----------------------------------
1         | 1/5/2015  | Counseling
1         | 1/12/2015 | Counseling
1         | 1/19/2015 | Counseling
2         | 1/21/2015 | Sup. Group

Client Locations (locations) Used only in the SELECT statement

id_client | city
----------------------
1         | Boston, MA
3         | Providence, RI

Here's The Query

SELECT clients.clientName,locations.city
FROM clients
LEFT JOIN locations ON clients.id_client=locations.id_client
LEFT JOIN services ON clients.id_client=services.id_client
WHERE services.serviceType='Counseling'

The Results

clientName | city
-----------------------
Abby       | Boston, MA
Abby       | Boston, MA
Abby       | Boston, MA

So it's giving me Abby living in Boston three times instead of the desired one.

Now, I know exactly why this is happening. The LEFT JOIN used for the services table is being used for the results and Abby's three counseling sessions are causing the city to be repeated three times.

Is there another way to do this JOIN so that the services table doesn't cause repetition like this? I've tried INNER JOIN and get the same thing.

Upvotes: 2

Views: 57

Answers (5)

tonirush
tonirush

Reputation: 430

You simply can use a distinct clause to avoid getting double results.

SELECT distinct clients.clientName,locations.city
FROM clients
LEFT JOIN locations ON clients.id_client=locations.id_client
LEFT JOIN services ON clients.id_client=services.id_client
WHERE services.serviceType='Counseling'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269653

Use exists:

SELECT c.clientName, l.city
FROM clients c JOIN
     locations l
     ON c.id_client = l.id_client
WHERE EXISTS (SELECT 1
              FROM services s
              WHERE c.id_client = s.id_client AND
                    s.serviceType = 'Counseling'
             );

Although you can use group by or distinct, this method should perform better. There is no need to generate the duplicated results just to remove them in another step.

Upvotes: 2

dotjoe
dotjoe

Reputation: 26940

You can get the distinct client ids for that serviceType and then join to client and location to get more details on the client.

SELECT clients.clientName,locations.city
FROM 
(Select distinct id_client from services WHERE services.serviceType='Counseling') s
INNER JOIN clients ON clients.id_client = s.id_client
LEFT JOIN locations ON clients.id_client=locations.id_client

Upvotes: 1

jpw
jpw

Reputation: 44881

Either use distinct

SELECT DISTINCT clients.clientName,locations.city
FROM clients
LEFT JOIN locations ON clients.id_client=locations.id_client
LEFT JOIN services ON clients.id_client=services.id_client
WHERE services.serviceType='Counseling'

Or a group by

SELECT clients.clientName,locations.city
FROM clients
LEFT JOIN locations ON clients.id_client=locations.id_client
LEFT JOIN services ON clients.id_client=services.id_client
WHERE services.serviceType='Counseling'
GROUP BY clients.clientName,locations.city

Or a subquery

SELECT clients.clientName,locations.city
FROM clients
LEFT JOIN locations ON clients.id_client=locations.id_client
LEFT JOIN (
  SELECT id_client, serviceType 
  FROM services 
  GROUP BY id_client, serviceType 
) services ON clients.id_client=services.id_client
WHERE services.serviceType='Counseling'
GROUP BY clients.clientName,locations.city

Sample SQL Fiddle

Upvotes: 2

O. Jones
O. Jones

Reputation: 108651

You are summarizing a detail result set. So do that using DISTINCT.

SELECT DISTINCT clients.clientName, locations.city
  FROM clients
  LEFT JOIN locations ON clients.id_client=locations.id_client
  LEFT JOIN services ON clients.id_client=services.id_client
 WHERE services.serviceType='Counseling'

Or use a GROUP BY query and provide some summary statistics:

SELECT DISTINCT clients.clientName, locations.city,
       COUNT(*) service_count
  FROM clients
  LEFT JOIN locations ON clients.id_client=locations.id_client
  LEFT JOIN services ON clients.id_client=services.id_client
 WHERE services.serviceType='Counseling' 
 GROUP BY clients.clientName, locations.city

Upvotes: 0

Related Questions