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.