MySQL 5.7: Difficulty with subquery returning multiple rows in JOIN causing unexpected results
I'm reviewing some code and Hey everyone, I'm running into an issue that's driving me crazy. I'm working with an scenario with a query in MySQL 5.7 where a subquery in a JOIN condition returns multiple rows, leading to unexpected behavior. I have two tables: `orders` and `customers`. The `orders` table has a foreign key referencing the `customers` table. Here's my current query: ```sql SELECT o.order_id, o.order_date, c.customer_name FROM orders o JOIN customers c ON c.customer_id = ( SELECT customer_id FROM customers WHERE c.city = customers.city ); ``` The intention is to join `orders` with `customers` based on the city. However, I'm getting the following behavior: ``` behavior Code: 1242. Subquery returns more than 1 row ``` I've tried using `LIMIT 1` in the subquery, but that didn't yield the results I needed. My goal is to get all orders along with the customer's name, while also ensuring that if there are multiple customers from the same city, they should all be represented in the result set. I would appreciate guidance on how to restructure this query to handle multiple rows in the subquery properly, or if there’s a better way to accomplish this join. Additionally, I’ve considered using a common table expression (CTE) but would like to know if that's a viable approach given my MySQL version. Any advice would be greatly appreciated! I'm working on a service that needs to handle this. Thanks in advance! I'm working with Sql in a Docker container on Ubuntu 20.04. Any feedback is welcome!