Retrieving a list of interactions between several factors 1

[russellobrien]

Dear All,

I am developing a simple pharmacology database. I have an Access DB with a list of drugs, list of drug classes and a list of class to class interactions.

What I wish to achieve is to put a list of drug classes into a query (say 5 or 6) and then return a list of all the interactions pulling in the name of the class from its table based on the ID stored in the interaction table.

I think using IN twice for each class may work, but this will presumably produce duplicates especially of the same class.

Any elegant (or non-elegant) solutions appreciated!

Russell

 

[mas5]

If I'm understanding your question then I think what you want is:

select drug_class.class_name, class_to_class_interaction.interaction
from drug_class, class_to_class_interaction
where drug_class.classid = class_to_class_interaction.classid and
(drug_class.classid = [first drug classid entered] or
drug_class.classid = [second drug classid entered], etc.)

 

[russellobrien]

Not quite,

What I have is a table of class names with an ID, and in the interactions table I have 2 class ID's in seperate columns and a description.
I can quite easily get the ID's of each interaction by using 2 IN statements.
What I would like to do is to save a second call to drug classes for the actual NAME column based on all the ID's returned from the first query, pull in the NAME column as part of the same query.
However, this involves pulling 2 different names for each INTERACTION CLASS ID from the same table (CLASSES) and into 2 new colums. I can generate one name column quite easily with the SQL below using a UNION but there are duplicates for all returns and I still only have one name (as the others are integer ID's still....)

I am stuck!

Russ

SELECT DRUG_CLASS_CLASS_INTERACTIONS.CLASS1, DRUG_CLASS_CLASS_INTERACTIONS.CLASS2, DRUG_CLASSES.NAME as NAME1, DRUG_CLASSES.NAME AS NAME2,
FROM DRUG_CLASS_CLASS_INTERACTIONS, DRUG_CLASSES WHERE CLASS1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) AND CLASS1=DRUG_CLASSES.ID
UNION
SELECT DRUG_CLASS_CLASS_INTERACTIONS.CLASS1, DRUG_CLASS_CLASS_INTERACTIONS.CLASS2, DRUG_CLASSES.NAME AS NAME1, DRUG_CLASSES.NAME AS NAME2,
FROM DRUG_CLASS_CLASS_INTERACTIONS, DRUG_CLASSES WHERE CLASS2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) AND CLASS2=DRUG_CLASSES.ID
ORDER BY SEVERITY DESC;

 

[mas5]

It would be helpful if you could post the relevant parts of your table structure. From your first note I couldn't tell that you had 2 class ID's in one row of a table. I don't know much about pharmacology, but is that a normalized table? (Will there always be 2 and only 2 class ids?) Also, it would be helpful if you could post what you wanted the report to look like. From what I see above below is what I'm guessing might work. You also have an order by SEVERITY, but I don't see that field in your select statement.

SELECT DRUG_CLASS_CLASS_INTERACTIONS.CLASS1,
DRUG_CLASS_CLASS_INTERACTIONS.CLASS2,
FIRSTDRUG_CLASSES.NAME,
SECONDDRUG_CLASSES.NAME
FROM DRUG_CLASS_CLASS_INTERACTIONS,
DRUG_CLASSES as FIRSTDRUG_CLASSES,
DRUG_CLASSES as SECONDDRUG_CLASSES
WHERE CLASS1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) AND
CLASS1=FIRSTDRUG_CLASSES.ID and
CLASS2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19) AND
CLASS2 = SECONDDRUG_CLASSES.ID

 

[russellobrien]

Thanks,

That certainly has given me a working variant. I modified this and turned it into a union searching for class interactions class 1 v 2 and then 2 v 1 as there is only one pairing. This works although it brings back each pair twice.
Is there a way to remove duplicates based on only a couple of columns rather than the whole row?

But thanks a lot- I was not aware that the same column could be called twice with different names and this has largely solved the problem!

Russ

 

[mas5]

Russ -

The solution presented isn't calling the same column twice with different names. It is essentially giving you two copies of the DRUG_CLASSES table so you can do independent lookups in each table.

I'm still confused about what it is that you are trying to do so I can't answer your question. I don't understand why you need the union in your query. I'm guessing you get each pair twice because of the union (one from each of the two queries). Post the relevant part of your table layouts, the results you want to get, the query you are currently using and maybe some sample data. That would be very helpful. I don't know of any way of removing duplicates from a query based on more than one field (use distinct). You can suppress duplicates when printing a report, but it would make more sense if you could eliminate them from the query.

 

[russellobrien]

Thanks again for all your help on this:

My tables are as follows: (abbreviated for clarity)

DRUG_CLASSES

ID NAME
1 Anti-arrhythmic (Other)
2 Calcium channel Antagonist
3 Beta-blocker
4 ACE Inhibitor
5 Analgesic (NSAIDS)
6 Proton pump inhibitor

DRUG_CLASS_CLASS_INTERACTIONS (also has description cols)

ID CLASS1 CLASS2
1 Coumarin Anticoagulant Analgesic (NSAIDS)
2 Coumarin Anticoagulant Antibiotic (Macrolide)
3 ACE Inhibitor Analgesic (NSAIDS)
4 ACE Inhibitor Aldosterone Antagonist
5 Diuretic (Loop) Diuretic (Thiazide)

class-class interactions are entered as integers linked to drug_classes table so class1 and class2 above are INTEGERS rather than text for speed and size.
what i want is to pull the text names in from the classes as well as the ID so further queries can use the ID and simple user interface can use the name. I suppose I could get the ID's and then simply query the DB for the names as a seperate query for each ID, but was hoping to get it all in one table.
this query below gets all v quickly by using IN for class 1 v 2 and then OR'ing an IN for class 2 v class 1 as a pair of interactions are only entered once (i.e either as CLASS_A v CLASS_B or as CLASS_B v CLASS_A but not both ways around).However, it brings back the class interactions in both directions as the row is not identical because the names are the opposite way round.

SELECT DRUG_CLASSES1.NAME AS DRUG1, DRUG_CLASSES2.NAME
AS DRUG2, DRUG_CLASS_CLASS_INTERACTIONS.*
FROM DRUG_CLASS_CLASS_INTERACTIONS,
DRUG_CLASSES AS DRUG_CLASSES1, DRUG_CLASSES AS
DRUG_CLASSES2
WHERE (CLASS1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,24) AND CLASS1=DRUG_CLASSES1.ID) AND
(CLASS2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,18,17,19,24) AND CLASS2=DRUG_CLASSES2.ID)
UNION
SELECT DRUG_CLASSES1.NAME AS DRUG1, DRUG_CLASSES2.NAME AS
DRUG2, DRUG_CLASS_CLASS_INTERACTIONS.*
FROM DRUG_CLASS_CLASS_INTERACTIONS,
DRUG_CLASSES AS DRUG_CLASSES1,
DRUG_CLASSES AS DRUG_CLASSES2
WHERE (CLASS1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,24) AND CLASS1=DRUG_CLASSES2.ID) AND (CLASS2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,18,17,19,24) AND CLASS2=DRUG_CLASSES1.ID)
ORDER BY SEVERITY DESC;

I think I am beginning to make more work for myself here than by processing the ID's seperately but any thoughts would be valued.

Russ

QUERY RESULT: NB: 1st and last rows same but names inverted

DRUG1 DRUG2 ID CLASS1 CLASS2
ACE Inhibitor Aldosterone Antagonist 4 4 12
ACE Inhibitor Analgesic (NSAIDS) 3 4 5
ACE Inhibitor ACE Inhibitor 14 4 4
Aldosterone Antagonist ACE Inhibitor 4 4 12

 

[mas5]

Russ -

I have some questions for you. For each item in Drug_Class_Class_Interactions, does both Class1 AND Class2 have to be in the list can Class1 OR Class2 be in the list? If you are looking for Class1 AND Class2 then just the first part of the query without the Union should get you everything you need without any duplicates. However, if, for each item in Drug_Class_Class_Interactions, either Class1 or Class2 have to be in the list then you have a different situation. With the Class1 or Class2 situation you can have three different scenarios:

a. Class1 in list and Class2 in list
b. Class1 in list and Class2 not in list
c. Class1 not in list and Class2 in list

Would scenarions b and c ever happen?

You can also try the query below. Right now I can't figure out if you'll get duplicates or not. If I'm thinking clearly you should not get any duplicates.

SELECT DRUG_CLASSES1.NAME AS DRUG1, DRUG_CLASSES2.NAME
AS DRUG2, DRUG_CLASS_CLASS_INTERACTIONS.*
FROM DRUG_CLASS_CLASS_INTERACTIONS,
DRUG_CLASSES AS DRUG_CLASSES1, DRUG_CLASSES AS
DRUG_CLASSES2
WHERE (CLASS1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,24) OR
CLASS2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,24) AND
CLASS1=DRUG_CLASSES1.ID) AND
(CLASS2=DRUG_CLASSES2.ID);


If I'm not thinking clearly and you do get duplicates, then you can try the union query below where 1) the first select pulls in all the items where Class1 is in the list (covers scenario a and b above) and 2) the second select would pull in items where class2 is in the list, but class1 was not in the list (covers scenario c above). So your query should look like:

SELECT DRUG_CLASSES1.NAME AS DRUG1, DRUG_CLASSES2.NAME
AS DRUG2, DRUG_CLASS_CLASS_INTERACTIONS.*
FROM DRUG_CLASS_CLASS_INTERACTIONS,
DRUG_CLASSES AS DRUG_CLASSES1, DRUG_CLASSES AS
DRUG_CLASSES2
WHERE (CLASS1 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,24) AND CLASS1=DRUG_CLASSES1.ID) AND
(CLASS2=DRUG_CLASSES2.ID)
UNION
SELECT DRUG_CLASSES1.NAME AS DRUG1, DRUG_CLASSES2.NAME AS
DRUG2, DRUG_CLASS_CLASS_INTERACTIONS.*
FROM DRUG_CLASS_CLASS_INTERACTIONS,
DRUG_CLASSES AS DRUG_CLASSES1,
DRUG_CLASSES AS DRUG_CLASSES2
WHERE (CLASS1 NOT IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,24) AND CLASS1=DRUG_CLASSES1.ID) AND (CLASS2 IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,18,17,19,24) AND CLASS2=DRUG_CLASSES2.ID)
ORDER BY SEVERITY DESC;

There is an assumption I've been making that may be incorrect. I'm assuming that you will always have something in both Class1 and Class2. If this is NOT the case, then both queries above will leave out the results of the items where Class1 or Class2 are blank.

Finally, I'm confused by the union query you posted. The only difference I see in the two select statements of the query is that in the first one you get the description of Class1 from the first copy of the Drug_Classes table and the description of Class2 form the second copy of the Drug_Classes table and in the second select statement it is the other way around. That's why the names are inverted between your first and last rows in the example above. I would think that you are still getting duplicates for each of your entries, but the duplicates all have the names inverted.

Let me know how this works out.

 

[russellobrien]

Thanks a lot again. I think your original replies have solved it. I don't think the union is required but for some reason I was persevering searching the classes both ways but this is not necessary. The first half of the union performs the correct way.

Again, thanks for all your help!!