Aggregate query question

Question

Aggregate query question

felipe.coury

Grasshopper

Points: 10
More actions
June 4, 2008 at 4:35 pm

#124132

I think this problem is classic - I am pretty sure I saw this one somewhere else but can't seem to find an easy solution.
I hava data on tables that goes like this:
id family_id attribute_value_id
--- --------- ------------------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 1
7 2 2
8 2 3
9 2 5
What I need to determine is, given a set of attribule_value_id's, what is the family_id. Please note that (1,2,3,5) should return 2 while (1,2,3,4,5) should return 1 and (1,2,3,4) should return NULL.
Is there an easy way to achieve that?
Any thoughts, suggestions and comments are more than welcomed.
Thanks in advance,
Felipe Coury

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004694 More actions · Answer 1

Sounds a lot like homework... don't mind helping, but let's see what you've tried... also, take a look at the URL in my signature... you'll get a better answer quicker if you take it to heart. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

felipe.coury Grasshopper Points: 10 More actions · Answer 2

It's not homework, I swear (does it make any difference). If you think I am a teen looking for homework answers, take a look at my blog:

http://blogs.felipecoury.com (It's in portuguese, so you may need a Google translation or also see http://felipecoury.wordpress.com).

I am a long time developer, but that tricks me a lot! 🙂

I had an insight, but I am not sure that is the best approach. I can pass the ids along with the number of ids, by using:

SELECT family_id, COUNT(attribute_value_id) FROM table_name

WHERE attribute_value_id IN (1, 2, 3, 4, 5)

GROUP BY family_id

HAVING COUNT(attribute_value_id) = 5

- OR -

SELECT family_id, COUNT(attribute_value_id) FROM table_name

WHERE attribute_value_id IN (1, 2, 3, 5)

GROUP BY family_id

HAVING COUNT(attribute_value_id) = 4

- OR -

SELECT family_id, COUNT(attribute_value_id) FROM table_name

WHERE attribute_value_id IN (1, 2, 3, 4)

GROUP BY family_id

HAVING COUNT(attribute_value_id) = 4

Do you think it covers all cases?

Jeff Moden SSC Guru Points: 1004694 More actions · Answer 3

It's not homework, I swear (does it make any difference).

Yes, it does make a difference... I'm more prone to help someone (anyone, not just students) that shows that they've really tried before simply giving up and hitting the boards... amongst those, I'm more willing to help those that post data that can be consumed without modification 😉

Anyway...

What do you have in place to ensure that the attribute_value_id isn't ever duplicated within the same family? That would be an essential key to success here... if you can guarantee no dupes there, then you're probably golden... and if the correct index is on attribute_value_id and family_id, then you've probably got the best performer, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)