user3264863 user3264863 - 4 months ago 9
MySQL Question

I need perfect mysql query to get result as i want

i have created sql fiddle at http://sqlfiddle.com/#!9/b6015c6/2

My table schema are as under :

> assignedattributeid (TABLE)
> id INT(10)
> productid INT(10)
> attributeid INT(10)
>
> products (TABLE)
> id INT(10)
> productname VARCHAR(50)


Suppose records are as under :

Table : assignedattributeid

id productid attributeid
1 5 10
2 5 11
3 6 11
4 7 10


Table : Products

id productname
5 P1000
6 P2000
7 P3000


Actually i want to get all those products where both attributeid 10 and 11 are assigned.


I use this query : select distinct products.* from products left
join assignedattributeid on assignedattributeid.productid=products.id
where assignedattributeid.attributeid in (10,11)


But it does not work. It shows all records. Actually it should show only P1000 product.

Any suggestion in query ?

Answer

Try this

select distinct products.productname as sid from  products
left join assignedattributeid on assignedattributeid.productid=products.id
where assignedattributeid.attributeid in (10,11)
group by sid having count(*) > 1