Finding association using BRIDGE table

Finding association using BRIDGE table
Hi all , just wanted to ask if there is any efficient way of querying the bridge table to find associations i.e. which two/more car options are sold together very often or if a patient has dignosis 'A' then he/she also have diagnosis 'B' quite often . So my query result should show diagnosis A and B occured together say 20 times in a particular dataset or people bought option1 ,2 and 3 together 30 times when purchasing a car .

Any thoughts ? also, if bridge table is avoided then what is the alternate for this ?

bridge table fields : Option_group key , optio_ID etc.

You wouldn't easily be able to report directly off the bridge table.  Instead you would want to create a separate fact table that holds the data on which associations are seen together.  This is market basket analysis problem which typically require a bit of processing and storage.

Take a look at http://rolandholtermann.wordpress.com/2012/05/06/market-basket-analysis-with-sql/ for an example with sales.  You can also read about it in The Data Warehouse Tookit.

For multiple choice scenarios (car options, patient diagnosis, etc.) I like to build a string of unique identifiers, which makes it easy to search combinations of options and/or exclude others.  If the number of options is very small (<26), then letters work well.  Otherwise a delimited string of numeric identifiers.  Examples: ABDMQ or |12301|14705|18234|29331| (make sure to include the leading and trailing delimiters!)

For example, to find cars that had option A and option B but not option D the SQL would look like this:
WHERE Options LIKE '%A%' AND Options LIKE '%B%' AND NOT Options LIKE '%D%'

To find patients with either of two diagnosis, it could look like this:
WHERE Diagnosis LIKE '%|13475|%' OR  Diagnosis LIKE '%|17455|%'

When using this approach, it is usualy in addition to a bridge table to represent the many-to-many relationship--some queries are bettered handled with pattern matching, and some relationaly using the bridge table.  But for finding when a particular option/diagnosis was not present the pattern matching approach is generally much easier.

If there are strong business need to use this kind of query then you might want to create fact table purely to answer these query needs. Else bridge table is an excellent solution to resolve the patient or Vehicle Options (M-M relationship).