SELECT from MYSQL with INNER JOIN on multiples of the same ID

Question

So im just wondering if the next is possible and if so how to approach this.

I created an INNER JOIN query to SELECT multiple ID's from on a table where it is equal to the ID value from another table.

Product table

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

Cultivar table

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

So i created an join where it will fetch all the cults_name based on the product tables cults_id1, cults_id2, cults_id3

Result:

Product_id(1) = mysql! , wood , fruit

Now the problem comes with the next one how can i display the one value that product_id(2) contains.

So it would result in looking like this:

Product_id(1) = mysql! , wood , fruit

Product_id(2) = choc

At this moment product_id(2) wont display in my listing because it has no resemble to the Cultivar table after the first ID, (using this query).

I know normalisation should be considered.

Query

   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

Now my product listing page has this to display it in listing but i want to display even if cults_id1, cults_id2, cults_id3 has an value of 0.

Product-listing.php

   <?php
      if($result = mysqli_query($conn, $query)){
      if(mysqli_num_rows($result) > 0){ 
      $i = 0;      
      while($row = mysqli_fetch_array($result)){
      $i++;

      echo "
      <p>Cultivars: <span>" .$row["cultivar_type1"]. "</span>, <span>" .$row["cultivar_type2"]. "</span>, <span>" .$row["cultivar_type3"]. "</span>, <span>" .$row["cultivar_type4"]. "</span></p> ";
      }
        mysqli_free_result($result);
     }

    }

Output:

  Product_id(1)  =  mysql! , wood , fruit

Expected:

  Product_id(1)  =  mysql! , wood , fruit

  Product_id(2)  =  choc

This looks like poor structure to me. What if there was a fourth cult? If you know normalisation should be considered, why aren't you considering it? — Strawberry
– Strawberry, Commented Oct 1, 2017 at 9:00
@Strawberry the cultivars comes from an multiple select, wil always be 4 or less than 4 .. and also would follow up on each other so cult_id_3 would never have a value if cult_id_2 does not have one, im so far in this small project that im just trying to accomplish something , moving back now would result in allot of changes. I am defs going to do this over but just for now looking for a way around this. — Ylama
– Ylama, Commented Oct 1, 2017 at 9:07
Just for now, I'd restructure your schema. You won't regret it. — Strawberry
– Strawberry, Commented Oct 1, 2017 at 11:58

Ylama · Accepted Answer · 2017-10-04 13:24:13Z

So the only thing that i needed to to was to create an LEFT JOIN. Because an LEFT JOIN can accept 0 or an value. And my query worked.

Yes this my mysql DB is not the normalized as would be expected.

Solution:

 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
  LEFT JOIN wine_cultivar c1 ON c1.cultivar_id = p.cultivar_1_id
  LEFT JOIN wine_cultivar c2 ON c2.cultivar_id = p.cultivar_2_id
  LEFT JOIN wine_cultivar c3 ON c3.cultivar_id = p.cultivar_3_id
  LEFT JOIN wine_cultivar c4 ON c4.cultivar_id = p.cultivar_4_id

Collectives™ on Stack Overflow

SELECT from MYSQL with INNER JOIN on multiples of the same ID

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related