How to select two columns from a multiple table query

IrishG · #1 (**permalink**) 11-10-10, 10:24

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

Code:

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.

Code:

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.

IrishG · #2 (**permalink**) 11-10-10, 10:36

Actually figured it out, well go it to work with

Code:

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech