Unable to join 3 tables properly

Question

While understanding natural joins, I came across the query:

Find the names of branches with customers who have an account in the bank and live in Harrison

The relational algebra expression from the book as follows:

Implementing the same with the query:

select distinct a.branch_name from depositor d, account a, customer where d.account_number=a.account_number and customer.customer_city='Harrison'; 

I get spurious tuples as follows:

+-------------+
| branch_name |
+-------------+
| Perryridge  |
| Downtown    |
| Brighton    |
| Redwood     |
| Mianus      |
| Round Hill  |
+-------------+
6 rows in set (0.00 sec)

But the query must have returned only Brighton and Perryridge based on the schema as follows:

mysql> select * from account;
+----------------+-------------+---------+
| account_number | branch_name | balance |
+----------------+-------------+---------+
| A101           | Downtown    |     500 |
| A102           | Perryridge  |     400 |
| A201           | Brighton    |     900 |
| A215           | Mianus      |     700 |
| A217           | Brighton    |     750 |
| A222           | Redwood     |     700 |
| A305           | Round Hill  |     350 |
+----------------+-------------+---------+
7 rows in set (0.00 sec)

mysql> select * from customer;
+---------------+-----------------+---------------+
| customer_name | customer_street | customer_city |
+---------------+-----------------+---------------+
| Adams         | Spring          | Pittsfield    |
| Brooks        | Senator         | Brooklyn      |
| Curry         | North           | Rye           |
| Glenn         | Sand Hill       | Woodside      |
| Green         | Walnut          | Stamford      |
| Hayes         | Main            | Harrison      |
| Johnson       | Alma            | Palo Alto     |
| Jones         | Main            | Harrison      |
| Lindsay       | Park            | Pittsfield    |
| Smith         | North           | Rye           |
| Turner        | Putnam          | Stamford      |
| Williams      | Nassau          | Princeton     |
+---------------+-----------------+---------------+
12 rows in set (0.00 sec)

mysql> select * from depositor;
+---------------+----------------+
| customer_name | account_number |
+---------------+----------------+
| Hayes         | A102           |
| Johnson       | A101           |
| Johnson       | A201           |
| Jones         | A217           |
| Lindsay       | A222           |
| Smith         | A215           |
| Turner        | A305           |
+---------------+----------------+
7 rows in set (0.00 sec)

Where am I making the mistake?

Answer 1

You didnt make join for customer table, your query should be like this

Select a.branch_name 
From depositor d 
  Join account a
    on d.account_number=a.account_number 
  Join customer as c
    on d.customer_name  = c.customer_name 
Where c.customer_city='Harrison'

I dont know how to join customer table to depositor maybe by name or if you have some key just replace it and you will get your result.

How to make joins in where clause useful link

Answer 2

You might forget the link between depositor and customer.

depositor.customer_name = customer.customer_name

So the entire query should be :

SELECT DISTINCT a.branch_name  
FROM depositor d, account a, customer  
WHERE d.account_number = a.account_number  
AND d.customer_name = customer.customer_name  
AND customer.customer_city='Harrison'

Result :

+-------------+
| branch_name |
+-------------+
| Perryridge  |
| Brighton    |
+-------------+
2 rows in set (0.00 sec)