Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: Query to combine all data from multiple tables into table

    Hi! I'm a SQL newbie and I'm working on database for an electronic store inventory and I need help Below are my schema:
    • Product(sku, brand, model, type)
    • Camera(sku, quantity, price)
    • Cellphone(sku, quantity, price)
    • Printer(sku, quantity, price)
    • Laptop(sku, quantity, price)

    **Note-I'm using the SKU # as the primary key to join each product type (camera, printer, laptop, desktop) table to the "Product" table.

    I would like to have a table of all the inventory in the store (a listing of ALL the item/products available in the store), I have the following query, but I'm stuck on the Select clause and I'm not sure if my Where clause is correct. In the Select clause, I'm not sure how to call the quantity and price column for each different item correctly.

    Select p.sku, p.brand, p.model, p.type, //incomplete-need help
    From Product p, Camera c, Printer pr, Laptop l, Cellphone ce
    Where p.sku = c.sku AND p.sku = pr.sku AND p.sku = l.sku AND p.sku = ce.sku //need verification
    Group by p.type;
    The result table that I want to have:

    SKU | brand | model | type | Quantity | Price |
    12345 | Canon | PowerShot | SX500 | Camera | 25 | 146.26
    67890 | Nikon | Coolpix L810 | Camera | 15 | 135.00
    89012 | Apple | iPhone 5S | Cellphone | 45 | 40 | 650.00
    34567 | Samsung | Galaxy S4 | Cellphone | 20 | 580.00
    45678 | Apple | MacBook Pro | Laptop | 10 | 1800.00
    90123 | Microsoft | Surface Pro 2 | Laptop | 10 | 1000.00
    78901 | Dell | B1160w | Printer | 15 | 93.00
    23456 | HP | Officejet Pro 8600 | Printer | 10 | 199.99

    Thank you in advance for your help!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you believe you need a separate table for each product type, when on the face of it they store common data (qty and price). yes if you are using the super/sub type model AND you have data that is distinct between say printers and mobile phones then by all means push that distinct data into a separate table, but keep ALL the common data in the product table

    whilst you are at it you could consider removing the quantity from any/all of these tables and instead use a transaction table which identifies stock movements. If you accouting policies require to track prices then you'd need to consider registering the value of the transaction as part of the transaction.


    so first step
    alter the product table
    ALTER TABLE Product
    ADD [COLUMN] Price DECIMAL(12,4)
    ADD [COLUMN] Quantity DECIMAL(12,4);

    note decide if you want or need decimal as the datatype for your quantity. its unusual, but not unheard to to have decimal stock values
    note decide if 12.4 is big / accurate enough for you price and or quantity columns

    then update those values.
    update products set products.quantity = camera.quantity, products.price = camera.price join camera on products.sku = camera.sku

    repeated for the other tables
    then drop those tables as they are irrelevant
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    2
    @healdem - Thank you for help. So if I have different specs for each individual product only then I would have separate tables? Would something like the below be correct in this case?

    Product(sku, brand, model, category, quantity, price)
    Camera(sku, pixels, fps)
    Cellphone(sku, os, screen_size)
    Printer(sku, type, color)
    Laptop(sku, os, ram)

Tags for this Thread

Posting Permissions

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