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, Sep 30 '17 at 13:32
ill edit, MySql... the thing is yu can have one product with more than one ciltivar @Parfait — Ylama, Sep 30 '17 at 13:35
I get what you say @Parfait, would be a big change in my whole program and mysql db, so without that its not possible? — Ylama, Sep 30 '17 at 13:39
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, Sep 30 '17 at 13:42
Shot i guess this how you learn, so you cant maybe show me a example or not amped for a headache...thanks for your input tho much appreciated. — Ylama, Sep 30 '17 at 13:45
What does this mean: *but dont think this is the way to go*? Describe error or current undesired results. And what is the name of actual table and field: *cultivars* and *cults_name* or *wine_cultivar* and *cultivar_type*? — Parfait, Sep 30 '17 at 13:51
Per latest edit, query should raise an error as *cultivars* table is never referenced in `FROM` or `JOIN` clauses. — Parfait, Sep 30 '17 at 14:08

Parfait · Accepted Answer · 2017-09-30T14:43:23.383

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 '17 at 14:43

answered Sep 30 '17 at 14:06

Parfait

104,375
17
94
125

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 Sep 30 '17 at 14:08
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? – Ylama Sep 30 '17 at 14:23
Whoops! I didn't use table aliases in `ON` clause. See update. – Parfait Sep 30 '17 at 14:44
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! – Ylama Sep 30 '17 at 14:54
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. – Parfait Sep 30 '17 at 14:58

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

1 Answers1