Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    2

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vanquish39 View Post
    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);
    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

    Code:
    SELECT pharmacy.businessName 
         , item.med_name
      FROM pharmacy
    INNER 
      JOIN junction 
        ON junction.pharmacy_id = pharmacy.pharmacy_id
    INNER 
      JOIN item 
        ON item.item_id = junction.item_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    This cannot wait for later. I must thank you now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •