Posting Freak
Posts: 6,092
Threads: 186
Joined: 2008-07
2011-12-12, 03:47 PM
(This post was last modified: 2011-12-13, 01:31 AM by Kalovale.)
Given a column with some duplicated values, how does one go about pulling out a subset of that column with all the unique values in it?
E.g: Given the relation Serves with attributes [pizzeria_name, pizza_name, price], I'm interested in knowing the names of the pizzas whose prices are under $10 everywhere they are served.
I can do a selection to get pizzas under $10, and I can project the names into a column, but I have no idea how to get the unique names only.
EDIT: I think I might be retarded and the relational algebraic projection actually already takes care of this.
Member
Posts: 192
Threads: 14
Joined: 2011-12
Gender: Female
Hold on, I'm tinkering with this right now, actually. I'll let you know in about 10 minutes if I find out. This actually makes me curious too.
EDIT: Derp. I got stuck when I was working on the relationship. HERP DERP. FFFF- nvm. ; _; I'm a letdown . . . Sorry, Kalovale.
EDIT2: Actually, I remembered doing some sort of query on my project where I did a report, and it'll look up all weapons that are set at a specific rarity (1-15), but I don't think I had a query that enabled me to look up specific things that were above a specific amount. Ugh. If anybody here knows how to do that, I'd be interested in seeing too. -_-
Posting Freak
Posts: 6,092
Threads: 186
Joined: 2008-07
I found a way to cheat at the problem, it's quite frustrating that I still can't come up with anything decent, though.
1/ find all pizzerias that serve at least one of the pizzas we want (Person \join_{age>"30"} Eats \join Serves), project the pizzeria-pizza relations
2/ self-join with new schemas (pizzeria, pizza1) and (pizzeria, pizza2) with condition (pizza1 != pizza2). As a result, get the relations of pizzerias that serve MORE THAN ONE pizza
* This way, we rule out the pizzerias that serve only one type of pizza, because its pizza1 will coincide with pizza2, inevitably
For this particular database, we only have 2 types of pizzas that people over 30 years old eat, so this works out (whatever passes the filter serves 2 types of pizza and thus "every" type). For other databases, we can easily find the number of pizzas that people over 30 years old eat, and keep recursing step 2 until we rule out all the ones that don't serve EVERY one of them.
Realistically, it works, it's just not a one-size-fits-all solution.