-1

Table Structure :

+-------+--------+-------------+------------------+---------------+----------------+
| recid | cartid | attributeid | attributevalueid | attributename | attributevalue |
+-------+--------+-------------+------------------+---------------+----------------+
|  5156 |   7126 |          20 |               91 | color         | Blue           |
|  5155 |   7126 |          19 |               87 | size          | S              |
|  5154 |   7125 |          20 |               91 | color         | Blue           |
|  5153 |   7125 |          19 |               87 | size          | S              |
|  5152 |   7124 |           4 |                8 | Color         | White          |
|  5151 |   7124 |           3 |                5 | Size          | L              |
|  5150 |   7123 |           4 |                8 | Color         | White          |
|  5149 |   7123 |           3 |                5 | Size          | L              |
|  5148 |   7122 |           4 |                8 | Color         | White          |
|  5147 |   7122 |           3 |                5 | Size          | L              |
+-------+--------+-------------+------------------+---------------+----------------+

Expected output:

+-------+--------+-------------+------------------+---------------+----------------+
| recid | cartid | attributeid | attributevalueid | attributename | attributevalue |
+-------+--------+-------------+------------------+---------------+----------------+
|  5156 |   7126 |          20 |               91 | color         | Blue           |
|  5155 |   7126 |          19 |               87 | size          | S              |
|  5154 |   7125 |          20 |               91 | color         | Blue           |
|  5153 |   7125 |          19 |               87 | size          | S              |
+-------+--------+-------------+------------------+---------------+----------------+

I need to fetch multiple rows like size and colour by passing attributevalueid for same cartid...

1
  • 4
    I think you will have to elaborate on why the expected result is as it is. For starters what parameters are passed to the query, cartid? May 28, 2016 at 17:00

2 Answers 2

1

If I got it right, you need those cartid which have both specified attributes set to specified values (19, 87) and (20,91).

select * from myTable where cartid in (
  select t2.cartid 
  from myTable t2
  where t2.attributeid=20 and t2.attributevalueid=91 
     or t2.attributeid=19 and t2.attributevalueid=87
  group by t2.cartid 
  having count(t2.recid)=2
)
0
-1

If i got your question right, you want resultset of all rows for specific 'cartid' by passing value for 'attributevalueid' i.e. 91,87,8,5
You can simply achieve same as follows by passing 'attributevalueid' values in subquery :

select * from t where cartid in (select cartid from t where attributevalueid like '91');
select * from t where cartid in (select cartid from t where attributevalueid like '87');

and so on.

1
  • @Rushi Patel, Please elaborate how does this answer gets negative feedback since you wanted output only based on 'attributevalueid' and also it gives the exact desired result which you needed. Am I missing something over here or your question needs some changes?
    – sql-no
    May 30, 2016 at 7:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.