Select locations from two tables

Question

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

You could use a join or a union. See if this helps: http://www.techrepublic.com/article/sql-basics-query-multiple-tables/ — mtrueblood, May 17 '16 at 03:28
can you also post your desired output, it would be easier to understand — KP., May 17 '16 at 03:40

score 3 · Accepted Answer · answered May 17 '16 at 05:12

3

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

answered May 17 '16 at 05:12

Andrew

1,858
13
15

how do i select the `latitude` and `longitude` as query returning the listings? – Kim Kyo May 17 '16 at 05:28

Nagahornbill · Answer 2 · 2016-05-17T06:00:34.510

1

As per my understanding of your question, for any suburb - supplied by a visitor, you want to include all the listings where either User's address is the same as the suburb supplied or the Listing's address is the same as the suburb supplied.

Assuming addressable_id column is related to Id of Users table and Listings table, based on value in addressable_type column, you can use the following query to join and get the desired result:

Select l.*
 From Listings l
  inner join Addresses a on ((a.addressable_id = l.user_Id and a.addressable_type = 'App\\User') or (a.addressable_id = l.Id and a.addressable_type = 'App\\Listings'))
  inner join Addresses a1 On a1.addressable_id = a.addressable_id and a1.Suburb = 'Melbourne'

edited May 17 '16 at 06:00

answered May 17 '16 at 04:40

Nagahornbill

121
7

If your tables are big and have proper indexing, I guess you should be using inner join where possible for better performance. Anyways, you got your answer, so no worries.. you can check the following link for further information on inner join and left join .. [inner join vs left join](http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server) – Nagahornbill May 17 '16 at 21:11

score 0 · Answer 3 · answered May 17 '16 at 04:35

try this,

    SELECT 
            a.addressable_id AS `userid`, 
            b.first_name     AS `username`
    FROM 
            addresses AS a JOIN users AS b ON a.addressable_id=b.id
    WHERE 
            a.suburb = 'Melbourne';


    if < addressable_id > has relation with < id > in listing table, 



    SELECT 
            a.addressable_id AS `userid`, 
            b.first_name     AS `username`
    FROM 
            addresses AS a JOIN users    AS b ON a.addressable_id=b.id AND addressable_type='App\\User'
    WHERE 
            a.suburb = 'Melbourne'
    UNION
    SELECT 
            b.user_id AS `userid`, 
            c.first_name     AS `username`
    FROM 
            addresses AS a JOIN listings AS b ON a.addressable_id=b.id AND addressable_type='App\\Listing'
                           JOIN  users   AS c ON b.user_id=c.id
    WHERE 
            a.suburb = 'Melbourne';

Select locations from two tables

3 Answers3