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

Question

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.

Really consider normalizing your product table with one cultivar_id field where product_id will repeat for different cultivar_ids in a one-to-many relationship. This avoids complex querying with multiple self-joins. — Parfait
– Parfait, Commented Sep 30, 2017 at 13:32
ill edit, MySql... the thing is yu can have one product with more than one ciltivar @Parfait — Ylama
– Ylama, Commented Sep 30, 2017 at 13:35
It is possible but instead of one join you have to make four self join for each cultivar field like you are attempting. Quite a headache! — Parfait
– Parfait, Commented Sep 30, 2017 at 13:42

Parfait · Accepted Answer · 2017-09-30 14:43:23Z

4

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

edited Sep 30, 2017 at 14:43

answered Sep 30, 2017 at 14:06

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ylama Over a year ago

i was thinking i would have to do something like this adding an value to each tabel in query, oky thanks let me try this out quick

Ylama Over a year ago

it looks promising, without the cultivars the query above works but i get an error #1054 - Unknown column 'wine_cultivar.cultivar_id' in 'on clause' for this query?

Parfait Over a year ago

Whoops! I didn't use table aliases in ON clause. See update.

Ylama Over a year ago

i actually tried it before you updated it and thought that was the problem , but stil mine did not work,maybe had a typo, but your update works 100%! Thanks ill have a look in normalisation for this. Thanks for the input much appreciated!

Parfait Over a year ago

Great to hear and glad to help. Yes, read on normalization and apply as best as you can which saves from complex querying and helps in efficient storage. Good luck on project.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related