var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Connecting 2 tables
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
inner join item on (pharmacy_id = item_id);
Try 2: When I do this... I get no med names
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.
Originally Posted by Vanquish39
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
ON junction.pharmacy_id = pharmacy.pharmacy_id
ON item.item_id = junction.item_id
This cannot wait for later. I must thank you now!