CODEWITHSUNDEEP
mysqlsqlselect
user2579643
user2579643

Reputation: 1

Multiple MySQL queries from same table

Thanks in advance for the help.

I have a table labelled attributes. In that table is product item numbers (pin), and attribute numbers. Each attribute for a pin is in a separate row

Ex. 

pin   attribute
111     4
111     5
111     10
112     4
112     5
...

I am trying to find a query that will allow me to say "Select pin if attribute = 4 and attribute = 5"

The attributes would be color, size, etc.. so get all records that are red (4) and size Small (5).

In the example above, it would return pins 111 and 112.

Mike

Upvotes: 0

Views: 72

Answers (3)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select pin,
group_concat(distinct attribute order by attribute) as atts 
from attributes
where attribute in (4,5)
group by pin
having (atts = '4,5');

fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is an example of a "set-within-sets" query. I advocate doing this with aggregation and a having clause. For your example, this would look like:

select pin
from attributes
group by pin
having sum(attribute = 4) > 0 and
       sum(attribute = 5) > 0;

The reason I like this approach is because it is flexible. If you wanted attributes 4 or 5, the query would be:

having sum(attribute = 4) > 0 or
       sum(attribute = 5) > 0;

If you wanted 4 and 5 and nothing else:

having sum(attribute = 4) > 0 and
       sum(attribute = 5) > 0 and
       sum(attribute not in (4, 5)) = 0;

Upvotes: 0

sgeddes
sgeddes

Reputation: 62851

This should work for you using count with distinct:

select pin 
from attributes
where attribute in (4,5)
group by pin
having count(distinct attribute) = 2

This will return any pins that have both attributes 4 and 5.

Upvotes: 1

Related Questions