CODEWITHSUNDEEP
mysql
Jerry
Jerry

Reputation: 1007

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

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?

Upvotes: 1

Views: 1283

Answers (6)

Ramalingam Perumal
Ramalingam Perumal

Reputation: 1427

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

Upvotes: 1

yasmine
yasmine

Reputation: 19

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.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 0

sh88
sh88

Reputation: 106

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 :)

Upvotes: 2

AssenKhan
AssenKhan

Reputation: 566

PLs Try this

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

Upvotes: 0

James
James

Reputation: 3709

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

Upvotes: 2

Related Questions