phpmyadmin Question posted in
The official documentation can be found here.

SQL same column multi filter – Phpmyadmin

I am trying to query a few tables however to simplify it ill make it specific to one table.

table view

have a look at the table view i have the attribute_id column and each row has an attribute id and some sort of value. I would like to select values specific to an attribute id from the whole table.

To make it easier to understand in the values on the table above i would like all the rows in the table where all rows in which the “text_value” for “attribute_id” 1 is 02067 and the text value for “attribute_id” 2 is HEERA BROWN BASMATI RICE. I would like both of these conditions to match one product. One product is made unique by “product_id”

Example

table example

from this set of results i would like a return of all the rows where rows with attribute_id 25 have an integer_value of 11 and rows with attribute_id of 24 have a value of 6 (currently all do but in the future they wont) both conditions must match for one product_id for that product_id rows to be returned.

If you need more clarification please ask thank you.

Tags:

2

Answers


  1. 0

    This is a reasonably basic JOIN – I think you would benefit from reading up about SQL and how to construct joins as it will give you more ideas.

    In your example, if your table is called MyTable then you would use:

    SELECT * FROM `MyTable` WHERE `attribute_id` = 1 AND `text_value`='02067'
UNION
SELECT * FROM `MyTable` WHERE `attribute_id` = 2 AND `text_value`='HEERA BROWN BASMATI RICE'

    This will give you all rows where you have the combination of attributes you have requested. You would not have to use a UNION query, but doing so allows you to select data from different tables which appears to be what you are asking for.

    Login or Signup to reply.
  2. 0

    Given the edit to the original question and further comments I think I know what the question is asking, but it has not yet been confirmed. The OP appears to be asking for records where the same ‘product_id’ is listed as satisfying the two sets of related criteria and only if it matches both should it be returned.

    This can be done by joining the table to itself, and then setting criteria against each side of the join.

    In this example, again assuming that the name of the table is ‘MyTable’:

    SELECT mt1.* FROM `MyTable` mt1
  LEFT JOIN `MyTable` mt2
  ON( (`mt`.`product_id`=`mt2`.`product_id`)
      AND (`mt1`.`id`<>`mt2`.`id`) )
WHERE (`mt1`.`attribute_id`=25)
  AND (`mt1`.`integer_value`=11)
  AND (`mt2`.`attribute_id`=24)
  AND (`mt2`.`integer_value`=6)

    There are other ways to do it – for example using IN (SELECT …) queries:

    SELECT * FROM `MyTable` mt0
  WHERE (`product_id` IN
    (SELECT `product_id` FROM `MyTable` `mt1`
      WHERE (`mt1`.`attribute_id`=25) AND (`mt1`.`integer_value`=11) ) )
  AND  (`product_id` IN
    (SELECT `product_id` FROM `MyTable` `mt2`
      WHERE (`mt2`.`attribute_id`=24) AND (`mt2`.`integer_value`=6) ) )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search