Hello Group,
I am trying to design a query for the following situation. I have 3
tables as follows:
1. criteria(CID int, CName varchar(20), DefWt int): Contains a set of
criteria for some purpose, each having an ID, a name and a default wt.
2. userdefcrweights(UserID int, CID int, weight int, primary key
(UserID, CID)): Table for storing the default criteria weights, if
they are changed by the user for him/herself. We will only store if
the default criteria wt. is different from the default 'default
criteria weight' in table criteria.
3. usercrweights(UserID int, ObjectID int, CID int, Weight int,
primary key(UserID, ObjectID, CID)): Finally, this table contain the
actual wt that the user want to use for an object for a criteria.
Again, this stores the row only if the value is different than the
defaults.
I want to get back the CID's, and the actual weights for them used by
a specific user for a specific object. I can do it with a series of
queries in a loop, but I was wondering if there is one query which can
do this. Also, I would welcome suggestion if this table structure and
design can be improved.
Thanks.

aquadoll <samikr (AT) gmail (DOT) com> wrote:
It can be done in one query if you use several EXISTS and CASE clauses
or similar, but the result would be a rather long and ugly query.

I recommend that you have three queries and some program logic:
If a select from "usercrweights" gives no result, select from
"userdefcrweights". If there is also no result, select the default value
from "criteria".

As an improvement I would add foreign key constraints between the tables,
so that nobody can delete a row from - say - "criteria" that is still
referenced by rows in "usercrweights".

Yours,
Laurenz Albe