MySQL select query with AND condition on same columns of same table

Question

I have a table like this

    itemid | propertyname | propertyvalue
___________|______________|_______________
     1     |   point      |  12
     1     |   age        |  10
     2     |   point      |  15
     2     |   age        |  11
     3     |   point      |  9
     3     |   age        |  10
     4     |   point      |  13
     4     |   age        |  11

I need a query to select all items where age greater than 10 and point less than 12.

I tried

`select itemid from table where (propertyname="point" and propertyvalue < 12)
 and (propertyname="age" and propertyvalue >10)`

It gives no results. How can I make it work?

Strawberry · Accepted Answer · 2016-09-14 11:39:22Z

2

you can use an inner join

SELECT
    a.itemid
FROM
    yourTable a
INNER JOIN
    yourTable b
ON
    a.itemid=b.itemid
AND a.propertyname='point'
AND b.propertyname='age'
WHERE
    a.propertyvalue<12
AND b.propertyvalue>10

ok so in table a youre lookin for all items with the name point and a value smaller 12 and in table b youre looking for all items with the name age and a value greater 10. Then you only have to look for items, which are in both tables. For this you connect the two tables over the itemid. To connect tables you use the join. Hope this will help you to understand. If not ask again :)

edited Sep 14, 2016 at 11:39

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Sep 14, 2016 at 10:53

sh88

1063 bronze badges

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

2 Comments

Shadow Over a year ago

If you provided some explanation as to how this code works, this would be a really good answer.

Honinbo Shusaku Over a year ago

@sh88 Add your explanation from the comments to your answer by editing it in case someone overlooks the comments

James · Accepted Answer · 2016-09-14 11:11:47Z

2

To join a table to itself in the same query you can include the table twice in the FROM clause, giving it a different alias each time. Then you simply proceed with building your query as if you were dealing with two separate tables that just happen to contain exactly the same data.

In the query below the table example is aliased as a and b:

SELECT a.itemid
FROM example a, example b
WHERE a.itemid = b.itemid
AND a.propertyname = 'point'
AND a.propertyvalue < 12
AND b.propertyname = 'age'
AND b.propertyname > 10

answered Sep 14, 2016 at 11:11

James

3,7492 gold badges43 silver badges39 bronze badges

Comments

Ramalingam Perumal · Accepted Answer · 2016-09-14 12:02:20Z

1

Try It:

SELECT itemid FROM test_table WHERE propertyname="point" AND propertyvalue < 12 AND itemid IN(SELECT itemid FROM test_table WHERE propertyname="age" AND propertyvalue >10)

http://sqlfiddle.com/#!9/4eafc6/1

edited Sep 14, 2016 at 12:02

answered Sep 14, 2016 at 11:55

Ramalingam Perumal

1,4283 gold badges18 silver badges47 bronze badges

Comments

AssenKhan · Accepted Answer · 2016-09-14 11:35:50Z

0

PLs Try this

select itemid from table where (propertyname="point" and propertyvalue < 12)
 or (propertyname="age" and propertyvalue >10);

answered Sep 14, 2016 at 11:35

AssenKhan

5665 silver badges15 bronze badges

Comments

Strawberry · Accepted Answer · 2016-09-14 11:42:05Z

0

Here's one idea...

SELECT item_id
     , MAX(CASE WHEN propertyname = 'point' THEN propertyvalue END point
     , MAX(CASE WHEN propertyname = 'age' THEN propertyvalue END age
  FROM a_table
 GROUP
    BY item_id
HAVING age+0 > 10
   AND point+0 < 12;

answered Sep 14, 2016 at 11:42

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

Strawberry · Accepted Answer · 2016-09-14 11:42:27Z

0

You can use an inner join. Meaning, it's like you're going to work with 2 tables: the first one you're going to select the name="age" and val>10, and the second one is where you're going to select name="point" and val<12. It's like you're creating an instance of your table that doesn't really exist. It's just going to help you extract the data you need at the same time.

edited Sep 14, 2016 at 11:42

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Sep 14, 2016 at 11:38

yasmine

191 silver badge4 bronze badges

Collectives™ on Stack Overflow

MySQL select query with AND condition on same columns of same table

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related