pascallammers pascallammers - 6 months ago 8
SQL Question

Get cross data of multiple tables in MySQL

I got the task to find the cluster number of a customer, based on where he is living.

For example:


  1. Customer Table - Teststraße 16

  2. Address Table - Look for Teststraße with number 16

  3. Give back the cluster ID of Teststraße

  4. And if it´s possible get back the number of the cluster ID from the Cluster-Table



Table Address:

address

Table Cluster:

cluster

Table Customer:

customer

I hope everything was understandable to you.

I appreciate your help!

Answer

you can look in the address table directly and join it with the cluster table:

SELECT c.number
FROM address AS a
INNER JOIN cluster AS c
ON a.cluster = c.id
WHERE a.street = 'Teststraße'
   AND a.street_number = 16

if you want to have the information about all customers, join it with this table as well and remove the where clause:

SELECT *
FROM customer
INNER JOIN address
ON customer.street = address.street
   AND customer.street_number = address.street_number
INNER JOIN cluster
ON address.cluster = cluster.id