skip to Main Content

I have a table with around 100 medicine interactions. I’m trying to make a statement where I insert X amount of drug names and it returns all rows where those drug names present in the first two columns.

If a patient has dicoumarol, warfarin and reserpine, it would return row 4 and 6 in the “Example of the table” picture below.

Example of the table: https://i.imgur.com/o6Nv39I.jpg

I use phpMyAdmin, which uses MySQL. I’ve tried a bunch of standard SELECT queries with a WHERE that says MedikamentA and MedikamentB equals the name of two drugs like:

SELECT * FROM MIdatabase 
WHERE MedikamentA = 'dicoumarol' 
  AND MedikamentB = 'ergotamine' " OR " MedikmentA = 'ergotamine' 
   OR MedikamentB = 'dicoumarol'

Not completely correct, but I wrote it top of my head. Probably need to switch an OR around and have some paranthesis.

I’m close to ripping a bit of hair off the top of my head trying to do this :p

3

Answers


  1. I think your query doesn’t give you what you are expecting because it is missing parenthesis.

    The easiest way to achieve what you want is to use IN()

    SELECT
      *
    FROM
      MIdatabase
    WHERE
      MedikamentA IN ('dicoumarol', 'warfarin', 'reserpine')
      AND MedikamentB IN ('dicoumarol', 'warfarin', 'reserpine');
    

    The above query is the same as the one below:

    SELECT
      *
    FROM
      MIdatabase
    WHERE
      (
        MedikamentA = 'dicoumarol'
        OR MedikamentA = 'warfarin'
        OR MedikamentA = 'reserpine'
      )
      AND (
        MedikamentB = 'dicoumarol'
        OR MedikamentB = 'warfarin'
        OR MedikamentB = 'reserpine'
      );
    
    Login or Signup to reply.
  2. Try something like this:

    SELECT * FROM MIdatabase 
    WHERE 'dicoumarol' IN ( MedikamentA, MedikamentB )
      AND 'ergotamine' IN ( MedikamentA, MedikamentB )
    
    Login or Signup to reply.
  3. You will need multiple LIKE statements along with the OR statement to meet the conditional requirements of calling for multiple drugs for each column. The second part of the LIKE statements needs AND as you will be referring to a separate column as part of the conditional statement.

    select * from table where MedikamentA like 'dicoumarol' or MedikamentA like 'warfarin' or MedikamentA like 'reserpine' and MedikamentB like 'dicoumarol' or MedikamentB like 'warfarin' or MedikamentB like 'reserpine';
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search