CODEWITHSUNDEEP
phpmysql
user3051762
user3051762

Reputation: 19

Select products by condition in another table

Iam working with joomla joomshopping component and now I faced a complicated problem. I need to select product from database which are in range of extra_field condition what imean by that.

We have two tables

jshopping_products

product_id | product_name | extra_field_1 | extra_field_2
1          | my_product   | 1             | 2
2          | my_product2  | 1             | 3 
3          | my_product3  | 4             | 3

extra_field_values

id         | field_id     | name          
1          | 1            | 100
2          | 2            | 200
3          | 2            | 300 
4          | 1            | 150

And i want to select all products in 100-120 range for extra_field_1 and 200-205 for extra_field_2 so result should be first product product

my sql which i tried look like this

$sql = mysql_query("SELECT a.* FROM jshopping_products a, jshopping_products_extra_field_values b WHERE (b.name >= 100 AND b.name <= 120 AND there is a problem ");

How can i pair this two tables?

EDITED *******

$query = "
SELECT  
  ".$prefix."jshopping_products.* 
FROM
  ".$prefix."jshopping_products
   JOIN ".$prefix."jshopping_products_extra_field_values field_2 ON ".$prefix."jshopping_products.extra_field_2 = field_2.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_7 ON ".$prefix."jshopping_products.extra_field_7 = field_7.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_8 ON ".$prefix."jshopping_products.extra_field_8 = field_8.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_9 ON ".$prefix."jshopping_products.extra_field_9 = field_9.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_10 ON ".$prefix."jshopping_products.extra_field_10 = field_10.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_11 ON ".$prefix."jshopping_products.extra_field_11 = field_11.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_12 ON ".$prefix."jshopping_products.extra_field_12 = field_12.id
   JOIN ".$prefix."jshopping_products_extra_field_values field_13 ON ".$prefix."jshopping_products.extra_field_13 = field_13.id
WHERE
  field_10.`name_cs-CZ` BETWEEN 0 and 999999999
";

thanks to respones i managed to make this anyway it gives me only 1 product and i know for sure in extra_field_10 there is about 100 values in range of 60-10000 with over 200 products

Upvotes: 0

Views: 117

Answers (2)

gwcoffey
gwcoffey

Reputation: 5919

SELECT distinct jshopping_products.*
FROM   jshopping_products
   JOIN extra_field_values field_1 ON jshopping_products.extra_field_1 = field_1.id
   JOIN extra_field_values field_2 on jshopping_products.extra_field_2 = field_2.id
WHERE
    field_1.name BETWEEN 100 AND 120 AND
    field_2.name BETWEEN 200 AND 205

Upvotes: 1

Barmar
Barmar

Reputation: 782584

You have to join with the extra_field_values table twice, once for each column in jshopping_products that you're joining with it

SELECT a.*
FROM jshopping_products a
JOIN jshopping_products_extra_field_values e1 ON a.extra_field_1 = e1.id
JOIN jshopping_products_extra_field_values e2 ON a.extra_field_2 = e2.id
WHERE e1.name BETWEEN 100 AND 120
AND e2.name BETWEEN 200 AND 205
GROUP BY a.product_id

Upvotes: 0

Related Questions