CODEWITHSUNDEEP
phpmysql
Doron Barel
Doron Barel

Reputation: 87

MySQL Complex Join - 4 Tables with multiple conditions

I need help creating a complex SQL query that is a little beyond my rather basic knowledge... I would greatly appreciate anyone's help!

See below for database structure. The bold row represents the tables; below are the relevant fields for each table.

I included below the desired output fields as well as conditions for the query.

Database Structure

custom_fields_data...........sales_listings.........sales.........listings

........owner_id......... .................listing_id.......... buyer_id...........id

.........field_id...............................sale_id..................id.............name

..........value..............................created_at

Database relationships:

custom_fields_data.owner_id = sales_listings.listing_id = listings.id

sales_listings.sale_id = sales.id

Conditions:

custom_fields_data.field_id = 4
sales.buyer_id = 12, 20, 21 or 30

Desired output:

listings.id
listings.name
custom_fields_data.value
sales_listings.created_at

Thank you, I really appreciate all your help.

Upvotes: 0

Views: 1126

Answers (1)

whihathac
whihathac

Reputation: 1761

This is very straight forward. Break your problem into 3 parts :

  1. What columns you want as output
  2. What tables you want data from and join conditions
  3. What are specific conditions

I see you have already broken the problem, but just haven't attempted to write the query.

I am no expert on MySQL, but in T-SQL, it would look like the following:

  SELECT listings.id
      listings.name
      custom_fields_data.value
      sales_listings.created_at    
    FROM custom_fields_data
       INNER JOIN sales_listings ON sales_listings.listing_id = custom_fields_data.owner_id
       INNER JOIN sales ON sales.id = sales_listings.sale_id
       INNER JOIN listings ON listings.id = sales.Id
    WHERE custom_fields_data.field_id = 4 
        AND sales.buyer_id IN (12, 20, 21, 30)

In your question, you havent clearly defined the relationship bettween Listing table and other tables. So I have assumed sales.id = listings.id.

Upvotes: 1

Related Questions