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
SD
*This post is locked for comments
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
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
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,902 Super User 2024 Season 2
Martin Dráb 229,316 Most Valuable Professional
nmaenpaa 101,156