M
Mike P.
There must be an easy way to do this, I have been struggling with it for
quite some time.
I have two tables joined in an M2M relationship. As an example, say I have
a table of errors that might be found in software products, and a table of
software products. A given error can occur in multiple products, and a
product can have multiple errors. Some products are more important than
others so a sort key is provided in the product table - lowest sort key is
most important. In this example, ALL errors have a corresponding software
product and all products have errors. A junction table is used to implement
the M2M relationship.
Now for the problem...
I am simply trying to list each error number with the highest ranked
software product where it occurred as determined by the product sort key. I
only want each error listed once along with its highest ranked product.
I have gotten this to work but there must be an easier (and faster) way.
Basically, I set up an outer join between error table and junction (E2J), an
outer join between junction and products (J2P), and an inner join between
these two outer joins (E2P). E2P has a row for each error / product
combination. I then use SQL similar to:
Select E2P.* from E2P where E2P.product id IN(select top 1 product id from
J2P where J2P.error id = E2P.error id);
This seems to work but is very slow and seems like I have found the most
complex solution to what is probably a simple problem. Below is an example
of the tables.
Your help is most appreciated.
error table
------------
error id
error desc
other error fields
product table
---------------
product id
sort key
other product fields
junction table
---------------
error id
product id
Mike P.
quite some time.
I have two tables joined in an M2M relationship. As an example, say I have
a table of errors that might be found in software products, and a table of
software products. A given error can occur in multiple products, and a
product can have multiple errors. Some products are more important than
others so a sort key is provided in the product table - lowest sort key is
most important. In this example, ALL errors have a corresponding software
product and all products have errors. A junction table is used to implement
the M2M relationship.
Now for the problem...
I am simply trying to list each error number with the highest ranked
software product where it occurred as determined by the product sort key. I
only want each error listed once along with its highest ranked product.
I have gotten this to work but there must be an easier (and faster) way.
Basically, I set up an outer join between error table and junction (E2J), an
outer join between junction and products (J2P), and an inner join between
these two outer joins (E2P). E2P has a row for each error / product
combination. I then use SQL similar to:
Select E2P.* from E2P where E2P.product id IN(select top 1 product id from
J2P where J2P.error id = E2P.error id);
This seems to work but is very slow and seems like I have found the most
complex solution to what is probably a simple problem. Below is an example
of the tables.
Your help is most appreciated.
error table
------------
error id
error desc
other error fields
product table
---------------
product id
sort key
other product fields
junction table
---------------
error id
product id
Mike P.