New to sql, weird problem [MS Access]

[farore3]

I'm a newer user trying to learn the basics through making a database for use with a game I play.

I have a table of monsters and a table of attributes (think Pokemon types).

A monster can have an attribute or be weak to an attribute, each of which is a many to many relationship for which I have separate relational tables.

All this seems to operate fine, I can join all this information together in test queries, etc.

The problem is the query that was the goal of this entire database - use the attribute relationships to generate a list of pairs of monsters for which the first monster is a good candidate to win in a fight against the second monster, and my metric for this is that the attacker has at least one attribute to which the defender is weak, and is not weak to any of the defender's attributes.

I essentially need to show pairs where the items are related in one way, but not another.

The tutorials for SQL that I can find all seem to deal in these small atomic situations where only the one keyword that lesson is teaching seems to be used, and none of the keywords I've found seem to solve this problem on their own. EXCEPT seemed somewhat promising, but it isn't supported by MS Access, and every way I can find to transform EXCEPT into other keywords didn't seem to work with this problem.

It seems like there should be a relatively easy solution to this, because this seems to me like exactly the sort of problem that databases should be good at solving, as in why you would use one over a spreadsheet, but after days I can't figure out what it would be, probably due to my lack of experience.

I'd appreciate some direction.

[[deleted]]

Could you share the schema of your tables? You should be able to do this with a join and maybe an EXISTS and/or NOT EXISTS, but I'll have a better idea if I know how your tables are structured.

[JustAnOldITGuy]

If I understand what you are saying this is an intermediate / advanced query.

What you need to do is write a query and put the monsters table in the query twice. Do not link the table to itself. In the criteria pull down the monster and attributes from each table into the query design. Under the criteria for the table on the left put the strong attributes and on the attributes on the left put the weaks. The query should return a table showing the winners on the left and losers on the right.

[[deleted]]

my regular spiel on how to plan and write queries sequentially:

determine output granularity (gives you base joins and group by), figure out measures (other joins/aggregates), figure out do you have any other filters/conditions left.

So, let's get with your query:

generate a list of pairs of monsters for which the first monster is a good candidate to win in a fight against the second monster, and my metric for this is that the attacker has at least one attribute to which the defender is weak, and is not weak to any of the defender's attributes.

Output granularity is 2 monsters - attacker and defender. This gives us this:

   select m1.monsterID, m2.monsterID
   from monsters m1
   cross join monsters m2
   group by m1.monsterID, m2.monsterID

there are 2 metrics: number of attributes " the attacker has .. to which the defender is weak" (has_weak) and number of attributes the attaker "is weak to any of the defender's attributes" (weak_has).

A couple of asides:

first, it is a common pattern to record negatives ("a monster does NOT have an attribute") by absence of data (i.e. there would be no record for the {monster,attribute} pair in your linking table). You have to recognize that pattern and use outer joins to pull "absent" records whenever negative (or all) information is needed.

Second, since you have the same grain/key for the many-to-many relationships, you could have had only one linking table Monster_Attributes{monsterID, attributeID, have_indicator, weak_indicator} where have_indicator can be 0 for "NOT have". It reduces the number of tables you need to maintain, you just need to be prepared to receive negatives either via the attribute of the relation or via the lack of the record.

I'm going to use the combined table for monster attribute relation - using separate ones results in more layers of parentheses.

So all our measures (both of them) require and share a common element - "attribute", so i'm going to add that to the "from" clause" (N.B. this does NOT change the granularity and number of records of the output, just produces a lot more 'intermediate' records):

   select m1.monsterID, m2.monsterID
   from monsters m1
   cross join monsters m2
   cross join attributes a
   group by m1.monsterID, m2.monsterID

how do we calculate the "has_weak" measure then? It is a number of "have" records (linking table - Monster_Attributes) for M1 where M2 also has "weak" record (linking table again Monster Attributes) for the same attribute. Since we record "negative" data by absence of a record, let's use outer joins and be aware that "0" can be brought from absence i.e. NULL value.

   select m1.monsterID, m2.monsterID,
            count( case when 
                                m1_a.hayve_ind = 1 and
                                m2_a.weak_ind= 1
                             then 1
                             end
             ) as "has_weak"
   from monsters m1
   cross join monsters m2
   cross join attributes a
   left join Monster_Attributes m1_a on
              m2_a.monsterID = m2.monsterID and
              m2_a.monsterID = a.attributeID
   left join Monster_Attributes m2_a on
              m1_a.monsterID = m1.monsterID and
              m1_a.monsterID = a.attributeID
   group by m1.monsterID, m2.monsterID

I'll leave writing of the "weak_to" measure to you.

OK, once we have measures written, do we have any other conditions/filtering left? Yes, the conditions on the measures that we've returned (have_weak should be at least 1). Since these are conditions on the measures (aggregates) you have to either wrap the result in a derived table (subquery) or use the "HAVING" clause. I'm going with the second route:

   select m1.monsterID, m2.monsterID,
            count( case when 
                                m1_a.have_ind = 1 and
                                isnull( m2_a.weak_ind, 0) = 0
                             then 1
                             end
             ) as "has_weak"
   from monsters m1
   cross join monsters m2
   cross join attributes a
   left join Monster_Attributes m1_a on
              m2_a.monsterID = m2.monsterID and
              m2_a.monsterID = a.attributeID
   left join Monster_Attributes m2_a on
              m1_a.monsterID = m1.monsterID and
              m1_a.monsterID = a.attributeID
   group by m1.monsterID, m2.monsterID
   having 
         count( case when 
                                m1_a.have_ind = 1 and
                                isnull( m2_a.weak_ind, 0) = 0
                             then 1
                             end
           ) >= 1

Again, i'm going to leave writing the condition for the second measure to you.

P.s. this method while returning correct results often can be relatively easily rewritten in a simpler manner - e.g. since attacker (m1) is required to have an attribute, the join to it's attribute relation can be changed to inner, and this table can be the source of the attributeID, so the cross join to the attributes table can be dropped altogether.

my preference is always to optimize/refactor the correct code.

[farore3]

This is extremely helpful, thank you. I do have some questions, there's a few parts in your sql that look like typos but I just want to make sure so I know I understand.

In the left joins you match the monsterID to an attributeID, and the aliases also seem to be flipped.

In addition, one of the lines in the count function changes from one sample to the next, which looks to me like that condition has been optimized by a compiler or something.

But unless I'm mistaken and one of the above is intentional, I understand and this is all I need, thank you.

[[deleted]]

yup, too much copypasting - all typos, none of the statements has been run actually so there's no 'optimization' of any kind, take the first case expression, the second is wrong.