Return only one row in subquery

I have the following query that I’m executing to show these records on a Laravel view.

$companies = DB::select(DB::raw("SELECT (
            SELECT f.name FROM fields f WHERE f.id = (
                SELECT fc.field_id FROM c_f fc WHERE fc.company_id = c.id  
            )
        )field, c.name, c.email, (
            SELECT name as country FROM countries WHERE id = (SELECT country_id from states WHERE id = (SELECT state_id from cities WHERE id = (SELECT city_id from addresses WHERE id = c.address_id))) 
        )country,
        (
            SELECT name as state_name FROM states WHERE id = (SELECT state_id from cities WHERE id = (SELECT city_id from addresses WHERE id = c.address_id)) 
        )states, (
            SELECT name as city FROM cities WHERE id = (SELECT city_id from addresses WHERE id = c.address_id) 
        )city
        FROM companies c WHERE c.user_id = 1")); 

I’m having issues with this line

SELECT name as city FROM cities WHERE id = (SELECT city_id from addresses WHERE id = c.address_id)

Because there are a lot of cities with the same name in my database, so it’s throwing an error, (probably the same with states but the error is being thrown at cities in this case, I tried to add LIMIT 1 at the end of the subquery but it’s giving me an error, so I guess I can’t use LIMIT in a subquery?

This is what i tried to change

SELECT name as city FROM cities WHERE id = (SELECT city_id from addresses WHERE id = c.address_id LIMIT 1)

This is how the tables look like

companies

addresses

cities

states

countries

How could I fix this?

Answer

You should really write your entire query as a JOIN. If it returns multiple rows it will be fairly easy to see where your duplication issue is. Note that based on what you have written, it is not due to cities having the same name, as you never select based on names. Without seeing data structures it’s hard to be certain but this should be close:

SELECT f.name AS field,
       c.name,
       c.email,
       r.name AS country,
       s.name AS states,
       m.name AS city
FROM companies c
JOIN c_f fc ON fc.company_id = c.id
JOIN fields f ON f.id = fc.field_id
JOIN addresses a ON a.id = c.address_id
JOIN cities m ON m.id = a.city_id
JOIN states s ON s.id = m.state_id
JOIN countries r ON r.id = s.country_id