Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010

    Unanswered: How to select two columns from a multiple table query

    Hi all,

    I'm new to SQL in general but I was wondering if someone could help me with a question I have.

    I have a single table that I'm trying to decompose into tables that are in 3nf. I'm working away on this and seem to be getting through this OK.

    I want to create three tables 'Orders', 'Items' and 'orderItems'.

    The orders table simply lists a sales person and customer and has a unique numeric ID for each order(done).

    The items table simply holds details of each item and is identified by a unique numeric identifier (done)

    The orderItems table will hold just two fields and they will be the primary key
    orderNum which references back to the orders table and itemID which references back to the Items table.

    I have been able to populate the orders and items table from the original non normalized table using SQL statements such as

    mysql> create table orders
        -> AS
        -> (SELECT origin.num,staff.fstName,staff.lstName,customer.customer
        -> from origin,staff,customer
        -> where staff.fstName = origin.fstName and staff.lstName=origin.lstName and customer.customer=origin.customer);

    however I can't figure out how to populate the orderItems table with just the order numbers and item id based on the data that is contained within the original table (which doesn't contain a item id just a description and price for each item.

    The closest I can get is using the following query but I just want the order number and item id out of it, can someone please point me in the right direction.

    mysql> SELECT DISTINCT items.itemID, orders.num, origin.item
        -> from origin,orders,items
        -> where items.item = origin.item AND items.price = origin.price AND orders.num=origin.num;

    Thanks G.

  2. #2
    Join Date
    Nov 2010
    Actually figured it out, well go it to work with

    mysql> INSERT INTO orderItems
        -> SELECT DISTINCT orders.num, items.itemID
        -> from items join origin orders join origin
        -> where items.item = origin.item AND items.price = origin.price AND orders.num=origin.num;

Posting Permissions

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