Vendor Properties for an Item, which table is it at?

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hello-

Could someone tell me which table an items primary vendor is located at? I have imported items into RMS to the main item table but noticed that there is no field there for vendor...my guess is because vendor is a multi-fielded table.

I am learning SQL and my guess would be that I would first run a query to insert the items then run an update query to the table that has the vendor linked to the item ILC via some joins, etc. Again, I am new at SQL and running these queries on test DB's before i go to breaking my own.

Any help would be appreciated.

Thanks

*This post is locked for comments

All responses (3)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

Re: Vendor Properties for an Item, which table is it at?

Hi Matt and Nicole-

Thanks for your reply. I am half way there...now what I want to do is find a way to insert into both of these tables. There is probably 7 fields i already know how to populate using an insert into statement to the item table, but I also want to insert into the supplierList table at the same time...is it possible to do this from one query? Right now I have, for simplicity sake, a query written that shows the ILC along with the 3 fields i would like to insert data into the supplierID.

Any suggestions would be appreciated. here is my query as a select..

select i.ItemLookupCode, s.supplierID, s.cost, s.reorderNumber

from Item as i inner join SupplierList as s

on i.ID = s.ItemID

I thought I could do the following..

1. write a simple insert into statement to insert into the item table.

2. use the query above but instead of a select, do an update statement but how would you write a sql update statement if you are updating a joined table?

thanks

joseph
Community Member Microsoft Employee on at

Like (0)

Report

Re: Vendor Properties for an Item, which table is it at?

The primary supplier field is found in the SupplierList table.

SupplierList has 2 joins to it so you can look up the other data. None of the tables use ILC as a join. Any reference to item in a foreign table is labelled as itemid which always relates to item.id.

item.id = supplierlist.itemid

supplier.id = supplierlist.supplierid
Nicole Hellerud 1,712 on at

Like (0)

Report

Re: Vendor Properties for an Item, which table is it at?

SD,

Thanks for your query about what table under items the vendor is located at. I believe that the table that you are looking for is SupplierID. Does this sound as the correct table that you are looking for?

Thanks Nicole