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?

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.

several comments...

first, there is absolutely no point doing any joins if all you want to retrieve is the item names

the SELECT clause should therefore also include the pharmacy name, right? i mean, otherwise the query can just pull from the item table and bob's your uncle

second, stay totally and completely away from USING, even if it's a valid option

third, in any query involving more than one table, you must always qualify every column used throughout the query

you will thank me for that advice later

This cannot wait for later. I must thank you now!