Deka

To add a bit of generic info to this discussion and hopefully aid your understanding of how databases work and how they complete queries, I'd like you to consider the following:

When working with data in databases we are always thinking in terms of a set of data. A SELECT * FROM x will produce a set of all X. SELECT * FROM x WHERE ... will produce a subset of x. When you go on to add grouping the database will reduce the set to a subset, and then start any counting or summing you have told it to do in the group. Any columns that are not counted, summed or have some other aggregate function performed on them must be declared to be group members, and each unique value they contain contributes to the grouping.

Because everything in a database realises a set, a set output from one part of a query can be used as input to another part of the query. SELECT * FROM (SELECT * FROM x WHERE ...) WHERE ...

is a perfectly valid query and the database will first create a subset and output the results, then take that subset and apply another filter, further reducing the size, then output the results again [actually, for the purists, it's sometimes a fib; the database will rewrite your query and in this case it would be perfectly valid to push the two where clauses together because they are effectively an AND.. but we'll leave that truth alone for now].

To effectively have a database answer your problem you must boil it down into sets and subsets, and then understand how sets of data come together.

JOIN connects sets of data horizontally. Some columns must be equal to allow the database to assert that the rows should be linked.

A JOIN B ->

**AB**

UNION connects sets of data vertically, and the number of columns must be equal

A UNION B ->

**A**

B

When you have GROUP prepare a set with aggregated results, the detail is irretrievably lost. There are ways of having the database perform aggregate operations alongside the main operation and putting the values into the detailed output, but these are analytical queries and I'll leave them alone. A WHERE clause is applied before a GROUP, and a HAVING clause is applied after.

SELECT a, count(b) as c_b FROM x WHERE y > 100 GROUP BY a HAVING c_b > 20

The above query finds everything in x having a y greater than 100, then creates a group of A and how many times each A value occurs, but only returns values of A where they occur more than 20 times (in the subset)

Have a think of how to state your problem in terms of sets..

In your problem