Post reply

Joining Multiple Tables

  • March 29, 2016 at 7:22 am

    #305989

    Hello Forum,

    I have a quick concept question. Here is the code:

    SELECT prod_name, vend_name, prod_price, quanity

    FROM orderitems, products, vendors

    WHERE products.vend_id = vendors.vend_id

    AND orderitems.prod_id=products.prod_id

    AND order_num =20005;

    My question: I understand that we are linking the order.product_id to the products.prod_id. I also understand that we are relating the product.vend_id to the vendors.vend_id. My question is, since we are using the "products" table in both equi-joins, that how the 4 columns are a linked together?

  • March 29, 2016 at 7:46 am

    #1868229

    Well SQL Server will link the tables together based on the joins you specified. Just making some hopefully not crazy assumptions about your table designs, you would get 1 row for each line in the order items tables that matches the order number, SQL server would join that to the products table so each order item would match 1 product, and then each product would be matched to the vendor.

  • March 29, 2016 at 8:18 am

    #1868250

    I don't recommend using 'comma' joins as this is not the standard and easier to make a mistake. Learn and use JOIN...trust me you'll be better off for it.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • March 29, 2016 at 8:21 am

    #1868253

    Great thank you ..helps a ton for me

  • March 29, 2016 at 8:22 am

    #1868254

    Fixed query

    SELECT prod_name,

    vend_name,

    prod_price,

    quanity

    FROM orderitems

    INNER JOIN products ON orderitems.prod_id = products.prod_id

    INNER JOIN vendors ON products.vend_id = vendors.vend_id

    WHERE order_num = 20005;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 29, 2016 at 8:29 am

    #1868259

    ok thank you helps a ton

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply