0

I have 2 tables, PRODUCT and VENDOR. The attributes for each are as follows:

PRODUCT: product_id, product_name, price, vendor_id

VENDOR: vendor_id, vendor_name, vendor_phone, vendor_address

I want vendor_name to show in PRODUCT where vendor_id in PRODUCT matches vendor_id in VENDOR without creating a view. I've looked into SELECT INTO and INSERT INTO, but I may be doing it wrong. I'm also doing this in Access as it's just dummy data for a classroom module.

user2540029
  • 11
  • 1
  • 3
  • Select into and Insert into are vastly different things. I don't either of these is at all what you want. Select into will create a new table and inset into will add rows to an existing table. I would STRONGLY advise not to denormalize your data like that. Keep those tables separate and create a query to return the values you want to see. – Sean Lange Jul 10 '14 at 14:28
  • Hmm that makes sense. How do developers usually do it though. I'm a business analyst and I see that in our company's database, `vend_id` and `vend_name` in the `PRODUCT` table matches the values in the `VENDOR` table. – user2540029 Jul 10 '14 at 14:40
  • Ugh vend_name should NOT be in the Product table. vend_id is the only vendor data that should be there. That is the foreign key. The rest of the data should come from the vendor table. In your original post I don't see vendor_name in the product table. – Sean Lange Jul 10 '14 at 14:46

1 Answers1

0

Based on your sample above I would write your query something like this.

    select product_id
    , product_name
    , price
    , vendor_name
    , vendor_phone
    , vendor_address
from Product p
inner join vendor v on v.vendor_id = p.v.vendor_id
Sean Lange
  • 33,028
  • 3
  • 25
  • 40