Hey guys, I'm a newbie to mysql and wanted to know how can I connect 2 tables together with a many-to-many relationship. This is what I have.

create table pharmacy
   pharmacy_id int not null primary key auto_increment,
   businessName varchar(25) not null,
   location varchar(25) not null
create table item
   item_id int not null primary key auto_increment,
   med_name varchar(25) not null,
   dosage varchar(5) not null
create table junction
   pharmacy_id int not null references pharmacy(pharmacy_id),
   item_id int not null references item(item_id),
   primary key (pharmacy_id, item_id)

Let's say there is 1 row in pharmacy and 3 rows in item. How can I connect the tables where it shows all the med_names in the items table

Try 1: When I do (inner join or left join)... I only get 1 med name

select med_name
from pharmacy
inner join item on (pharmacy_id = item_id);

Try 2: When I do this... I get no med names

select med_name
from pharmacy
inner join junction using (pharmacy_id)
inner join item using (item_id);

Any ideas on how I can write a statement where it joins the 2 tables and shows all the med names? Thanks guys. Please if you can provide the code, I would be

more than happy.

7 Years
Discussion Span
Last Post by drjohn
SELECT * FROM pharmacy p
LEFT JOIN junction j ON j.pharmacy_id = p.pharmacy_id
LEFT JOIN item i ON i.item_id = j.item_id

This is also a possibility, but this will not show a pharmacy if there are no junctions/items.

SELECT * FROM pharmacy p, junction j, item i
WHERE j.pharmacy_id = p.pharmacy_id
AND i.item_id = j.item_id

Edited by pritaeas: n/a


Thank you all. By the way, is there anyway to show all the mednames without having a junction table?

Edited by Vanquish39: n/a


Do you mean: just the med_names and nothing else? You can of couse select them directly from the items table. But this is not what you mean, I suppose. What do you mean instead? If you want to show some kind of relationship between items and pharmacies you do need the junction table for an m:n relation (if you don't want ugly solutions not in 3NF involving set fields and the like).


What I really wanted was this. I had another table called "Patient" it is very similar to pharmacy with attributes of firstname, lastname, address, etc etc. What I wanted was a way to connect the patient table and the item table so that when I do a query it will show me all the medicines that this patient is taking.

example of patient

create table patient
patient_id int not null primary key auto_increment,
firstname varchar(25) not null,
lastname varchar(25) not null,
age int(3) not null,

Can someone please tell me how I can connect this patient table and item table in such a way so when i do a query it will show all the medicines that this specific person is taking. thanks guys.


Set up a join table:

create table patients_and_items 
(patient_id integer not null
,item_id integer not null
, foreign key (patient_id) references patients (patient_id)
, foreign key (ktem_id) references items (item_id)


Then query:

select p.*, i.* from patients p, patients_and_items pi, items i
where p.patient_id=pi.patient_id and i.items_id = pi.items_id;

The medication doses and special advice for taking the medicine would also belong in the join table, e.g. in a remarks text field.


You'd also need details of WHO authorised the medication each time, who administered it, and probably other things too in the table above.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.