CODEWITHSUNDEEP
phpmysqljoin
Ylama
Ylama

Reputation: 2489

INNER JOIN mysql from one table to another where its based on multiples of same id

I have an table called product which lets say looks like this:

product

  product_id  |  cults_id1 |  cults_id2 | cults_id3
  -----------------------------------------------------
       1      |      5     |     4      |     2

And the also a table thats based on this called cultivar:

cultivar

  cults_id  |  cults_name | 
  -------------------------
       1    |  berries    |  
       2    |  fruit      |  
       3    |  choc       |  
       4    |  wood       | 
       5    |  mysql!     |

How would i create an JOIN query to get each name from cultivar table where the product id in product table is the same as cults_id in the cultivar table?

OUTPUT

My Product Cults : 

  berries, Mysql!, wood, fruit

Dont think an INNER JOIN is the way to go but i would have tried something like this:

  $query = "SELECT cultivars.cults_name
        FROM product 
        INNER JOIN wine_cultivar ON wine_cultivar.cults_id = product.cultivar_1_id
        INNER JOIN wine_cultivar ON wine_cultivar.cults_id = product.cultivar_2_id
        INNER JOIN wine_cultivar ON wine_cultivar.cults_id = product.cultivar_3_id
        INNER JOIN wine_cultivar ON wine_cultivar.cults_id = product.cultivar_4_id
        ";

i tried a inner join multiple times targeting all the ids but dont think this is the way to go. Also this is just a part of my sql query.

Upvotes: 1

Views: 182

Answers (1)

Parfait
Parfait

Reputation: 107622

Simply assign table aliases to each self join and then reference corresponding fields in SELECT.

Right now you join to same table but do not provide aliases to distinguish between the four which MySQL should have raised its Error #1066 for this attempt.

SELECT p.product_image_path, p.product_id, p.brand_name, p.product_name, b.botttle_size, v.vintage, 
       t.wine_type_blend, p.price, p.quantity, p.time_created, p.reference_number, p.shipping_cost, 
       c1.cultivar_type as cultivar_type1, c2.cultivar_type as cultivar_type2, 
       c3.cultivar_type as cultivar_type3, c4.cultivar_type as cultivar_type4
FROM product p
INNER JOIN wine_bottle b ON b.bottle_id = p.bottle_id 
INNER JOIN wine_vintage v ON v.vintage_id = p.vintage_id 
INNER JOIN wine_type t ON t.type_id = p.type_id
INNER JOIN wine_cultivar c1 ON c1.cultivar_id = p.cultivar_1_id
INNER JOIN wine_cultivar c2 ON c2.cultivar_id = p.cultivar_2_id
INNER JOIN wine_cultivar c3 ON c3.cultivar_id = p.cultivar_3_id
INNER JOIN wine_cultivar c4 ON c4.cultivar_id = p.cultivar_4_id

Upvotes: 4

Related Questions