CODEWITHSUNDEEP
phpmysql
uiTeam324
uiTeam324

Reputation: 1245

Get dependent records from table

Here is the table structure. I want to fetch all the id_product which have value 2 and 601(here id_product = 5). If i use OR all the records will be populated which is not necessary. 

     id_product    attribute_id  Value
        5               2         2
        6               2         2
        8               2         601
        6               2         601
        5               3         601
        8               3         32
        6               3         41

Any help would be appreciated. I don't want to use sub query :-p

Upvotes: 0

Views: 23

Answers (2)

fthiella
fthiella

Reputation: 49049

You can use a group by query:

select
  id_product
from
  tablename
where
  attribute_id=2 and values in (2,601)
group by
  id_product
having
  count(*)=2

this will select all products that have (attribute_id=2 and value=2) or (attribute_id=2 and value=601) in two different rows, and then it will count the number of rows returned and select only products that have two rows (one with value 2 and one with value 601).

Another option (it's not too clear from your question) is to use this where clause instead of the one on the query above:

where
  (attribute_id=2 and value=2) or
  (attribute_id=3 and value=601)

Upvotes: 1

rdn87
rdn87

Reputation: 724

You can use this query in your case:

SELECT * FROM nameTable WHERE Values IN (2,601) and attribute_id = 2

Upvotes: 0

Related Questions