Connecting 2 tables

[Vanquish39]

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.

[pritaeas]

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

[Vanquish39]

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

[smantscheff]

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).

[Vanquish39]

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.

[smantscheff]

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.

[drjohn]

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