Swaze Swaze - 1 year ago 54
SQL Question

Combining statements for one query. (Alter Table, Order By, Select)

I'm currently working on a basic database for Orders from customers. My issue is fields in one table (call it EquipmentPerOption) correspond to records in another table (Equipment). In theory adding a record to Equipment should add a new Column to EquipmentPerOption with the name of the new record.

For example:

**Equipment Table**
Equipment Price
Hose $1.00
Shovel $2.00
Hoe $3.00

**Equipment per Option Table**
OrderOption Hose Shovel Hoe
1 1 0 2
2 3 2 1
3 0 1 3
4 1 1 1

So basically I now have a button on a menu which takes me to an Add New Record screen for Equipment. How do I make it so that when I've finished adding the new record for Equipment it appears on the EquipmentPerOption table as a new Column? Ideally this:

OrderOption Hose Shovel Hoe (New Equipment)
1 1 0 2 0
2 3 2 1 0
3 0 1 3 0
4 1 1 1 0

I've been messing around with SQL and have come up with this as an SQL code for a query that will run after clicking a "Check" button at the bottom of the Equipment Form. (Obviously it will save the record before running the query)

ALTER TABLE EquipmentPerOption
( SELECT TOP (1) *
FROM Equipment
ORDER BY created_date DESC) Short Interger

So my question is why is this code wrong? And how do I fix it to achieve the desired outcome? Or have I set up the database wrong and should just start again with a different structure for the tables?

Thanks for reading.

Answer Source

If you find yourself having to add columns in order to handle simple transactions, it is a sign the db design is missing something.

Given your Equipment table:

Equipment Table
     Id   Equipment     Price
      1    Hose        $1.00 
      2    Shovel      $2.00 
      3    Hoe         $3.00 

A bundle of these would have a name and probably a price:

     Id   Name             Price
      1   Hoe And Hose      ...
      2   H-S-H Pkg         ...

Then a table to associate certain equipment records with a given bundle:

     Id   EquipmentId    Quantity    
      1      1              2       ; Bundle 1 has 2 hoses
      1      3              1       ;  and 1 hoe
      2      1              3       ; Bundle 2 has 3 hoses
      2      2              2       ;   and 2 shovels
      2      3              1       ;   and 1 hoe

Presumably all current equipment is already in the Equipment items table, creating a new Bundle is just a matter of creating a new EquipmentBundle record and associating the related items with it via the EquipmentBundleItems table.