Kim Kyo Kim Kyo - 6 months ago 9
SQL Question

Select locations from two tables

I am new to SQL and I'm having difficulties writing the following query.

Scenario

A user has two addresses, home address (

App\User
) and listing address (
App\Listing
). When a visitor searches for listings for a Suburb or postcode or state, if the user's listing address does not match - but if home address does match - they will be in the search result too.

For example: if a visitor searches for
Melbourne
, I want to include listings from
Melbourne
and also the listings for the users who have an address in
Melbourne
.

Expected output:

user_id first_name email suburb postcode state
1 Mathew mathew.afsd@gmail.com Melbourne 3000 VIC
2 Zammy Zamm@xyz.com Melbourne 3000 VIC


Tables

users:

id first_name email
1 Mathew mathew.afsd@gmail.com
2 Zammy Zamm@xyz.com
3 Tammy tammy@unknown.com
4 Foo foo@hotmail.com
5 Bar bar@jhondoe.com.au


listings:

id user_id hourly_rate description
1 1 30 ABC
2 2 40 CBD
3 3 50 XYZ
4 4 49 EFG
5 5 10 Efd


addresses:

id addressable_id addressable_type post_code suburb state latitude longitude
3584 1 App\\User 2155 Rouse Hill NSW -33.6918372 150.9007221
3585 2 App\\User 3000 Melbourne VIC -33.6918372 150.9007221
3586 3 App\\User 2000 Sydney NSW -33.883123 151.245969
3587 4 App\\User 2008 Chippendale NSW -33.8876392 151.2011224
3588 5 App\\User 2205 Wolli Creek NSW -33.935259 151.156301
3591 1 App\\Listing 3000 Melbourne VIC -37.773923 145.12385
3592 2 App\\Listing 2030 Vaucluse NSW -33.858935 151.2784079
3597 3 App\\Listing 4000 Brisbane QLD -27.4709331 153.0235024
3599 4 App\\Listing 2000 Sydney NSW -33.91741 151.231307
3608 5 App\\Listing 2155 Rouse Hill NSW -33.863464 151.271504

Answer

Try this. You can check it here.

SELECT l.*
FROM listings l
LEFT JOIN addresses a_l ON a_l.addressable_id = l.id
  AND a_l.addressable_type = "App\\Listing"
  AND a_l.suburb = "Melbourne"
LEFT JOIN addresses a_u ON a_u.addressable_id = l.user_id
  AND a_u.addressable_type = "App\\User"
  AND a_u.suburb = "Melbourne"
WHERE a_l.id IS NOT NULL OR a_u.id IS NOT NULL