Question 173267 in Mysql Question posted in
The official MySQL documentation can be found here.

Performing Many-to-Many query filter | MySQL

So, I have 4 tables:

  • Drugs
  • Manufacturers
  • Drug_manufacturers
  • drug_reports

Drugs table

id name
1 row 1
2 row 2

Manufacturers table:

id name
1 ДИГОКСИН ТАБЛ. 0,25МГ №40
2 DRUG NAME 54

Drug manufacturers table

drug_id manufacturer_id counter
1 3 1
1 45 2

And drug_reports table

id period_code drug_id manufacturer_id date
1 A100 1 3 01.01.2023
2 A101 1 45 01.01.2023
3 A102 2 3 01.01.2023

So i created as following query:

SELECT dr.id, dm.counter, dr.period_code, dr.drug_id, d.name, m.name FROM drug_reports dr
LEFT JOIN drugs d ON dr.drug_id = d.id
LEFT JOIN drug_manufacturers dm ON d.id = dm.drug_id
LEFT JOIN manufacturers m ON dm.manufacturer_id = m.id
WHERE dr.period_code LIKE '%72411 IM%'

It returnes:

result screenshot

Interestingly, id: 412790 and period_code: 72411 IM information is one record in table. As a result, it was returned several times. Too much data is being returned multiple times.
I also tried as following query, but it also returned incorrect data:

SELECT dr.id, dm.counter, dr.period_code, dr.drug_id, d.name, m.name FROM drug_reports dr
LEFT JOIN drugs d ON dr.drug_id = d.id
LEFT JOIN drug_manufacturers dm ON d.id = dm.drug_id
LEFT JOIN manufacturers m ON dm.manufacturer_id = m.id AND dr.mf_id = dm.manufacturer_id
WHERE dr.period_code LIKE '%72411 IM%'

I need as following result:

dr.id peroid_code name counter Manufacturer
412790 72411 IM ДИГОКСИН ТАБЛ. 0,25МГ №40 5 ARPIMED

How to write a proper query? Can anyone has idea?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The following query I made shows all the data in the linked table in the middle during a many-to-many join. That is

    SELECT dr.id, dm.counter, dr.period_code, dr.drug_id, d.name, m.name FROM drug_reports dr
LEFT JOIN drugs d ON dr.drug_id = d.id
LEFT JOIN drug_manufacturers dm ON d.id = dm.drug_id
LEFT JOIN manufacturers m ON dm.manufacturer_id = m.id
WHERE dr.period_code LIKE '%72411 IM%'

    So, I wrote following query:

    SELECT 
 dr.id,
 (SELECT dm.counter FROM drug_manufacturers dm WHERE dm.drug_id = dr.drug_id AND dm.manufacturer_id = dr.mf_id LIMIT 1 ) as COUNTER
 dr.period_code,
 dr.drug_id,
 d.name,
 m.name 
FROM drug_reports dr
LEFT JOIN drugs d ON dr.drug_id = d.id
LEFT JOIN manufacturers m ON dm.manufacturer_id = m.id
WHERE dr.period_code LIKE '%72411 IM%'

    It returned what i want result. And no any dublicate records in result. Thanks to everyone!


  2. 0

    Since there are multiple "names" for each drug, but you want only one name for each, the query is incorrectly structured. Here are ways to fix that:

    Plan A:

    SELECT ... MIN(m.name) AS name1
    FROM ... LEFT JOIN ...
    GROUP BY dr.id

    The MIN or MAX or ANY_VALUE, together with the GROUP BY, collapses multiple possible values into one.

    Plan B:

    SELECT ... 
       GROUP_CONCAT(m.name) AS alt_names
    FROM ... LEFT JOIN ...
    GROUP BY dr.id

    This is similar to Plan A, but strings out the alternate names in a commalist.

    Plan C:

    SELECT ...
    FROM ( SELECT ... FROM drug_reports WHERE period_code LIKE '%72411 IM%' ) AS dr
    LEFT JOIN ...

    This starts by picking only the one relevant row from drug_reports, then looks up the other info. If the tables are huge, Plan C is likely to be the fastest.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search