Multiple MySQL queries from same table

Question

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

are you still having problem with this query?

Praveen Prasannan
– Praveen Prasannan

2013-08-06 04:00:03 +00:00
Commented Aug 6, 2013 at 4:00 — Praveen Prasannan
– Praveen Prasannan, Commented Aug 6, 2013 at 4:00

sgeddes · Accepted Answer · 2013-07-13 17:55:34Z

1

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.

SQL Fiddle Demo

edited Jul 13, 2013 at 17:55

answered Jul 13, 2013 at 17:50

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sgeddes Over a year ago

@user2579643 -- np, glad I could help!

Gordon Linoff · Accepted Answer · 2013-07-13 18:21:26Z

0

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;

answered Jul 13, 2013 at 18:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Praveen Prasannan · Accepted Answer · 2013-08-31 15:09:02Z

0

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

edited Aug 31, 2013 at 15:09

answered Jul 13, 2013 at 18:49

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

Collectives™ on Stack Overflow

Multiple MySQL queries from same table

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related