Can someone help me write a query to find the items in the database that do not have a supplier associated with them?
- posted
17 years ago
Can someone help me write a query to find the items in the database that do not have a supplier associated with them?
SELECT * FROM ITEM WHERE SupplierID=0
- Evan Culver New West Technologies
Thanks.
I imported my Database and used > SELECT * FROM ITEM WHERE SupplierID=0
Thanks.
I imported my Database and used
Supplier you wish.
Bill,
Are you sure that the SupplierID in the SupplierList is a valid ID number of a supplier?
Is your import routine putting a number in the SupplierID in the Item table?
Like I said before, you _should_ be looking at the ID number in the Supplier table to confirm the correct one.
Understand what relationships you have/need;
The Supplier table has the complete info about who the supplier is. That ID number is the identifier for that one supplier.
The Item table's SupplierID references the Primary supplier ID of that item. The SupplierID relates back to the Supplier table.
The SupplierList table is the supplier's re-order number, price, minimum buy and MPQ for that item. There may be more than one entry per item. The SupplierID, again, relates back to the Supplier table. The ItemID relates back to the Item table.
When I try to run the query, I get en error depending on the ILC that I use. IE: CIR-ILC CF0161-8RH returns Line 1: Incorrect syntax near 'RH', ILC BRU-59104142 returns Invalid colum name 'BRU'
I use the first three to identify the manufacturer, then the mfg p/n. If there are options like shoe size and style (right or left hand) I use the 8RH or what ever the size and style is.
Bill
"Jeff" wrote:
Right now we are attempting to find out who the Primary Supplier is for a particular ILC.
item purchased from your Supplier in question?
correctly, otherwise it will show 0 for the SupplierID.
Right now we are attempting to find out who the Primary Supplier is for a particular ILC.
for an item purchased from your Supplier in question?
correctly, otherwise it will show 0 for the SupplierID.
Hi Jeff,
I ran the query and it does show the correct vendor name. I did the database check and re-indexed successfully.
When I check the ILC again, nothing still is in the Supplier Tab.
Anyother suggesti> Bill,
correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.
database check and re-index in SO Admin.
CF0161-8RH'
SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.
181 for an item purchased from your Supplier in question?correctly, otherwise it will show 0 for the SupplierID.
number of
the Supplier
that item.
minimum buy
message
message
the database
select sl.* from supplierlist sl inner join item i on sl.itemid = i.id where i.itemlookupcode = 'myILCGoesHere'
Glenn Adams Tiber Creek C> Hi Jeff,
correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.
database check and re-index in SO Admin.
SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.
181 for an item purchased from your Supplier in question?correctly, otherwise it will show 0 for the SupplierID.
directly into SO
ID number of
Item table?
the Supplier
is. That ID
that item.
minimum buy
Supplier table of
newsgroup reader
newsreader"
message
number when I
Manager, there is
message
the database
correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.
database check and re-index in SO Admin.
SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.
181 for an item purchased from your Supplier in question?imported correctly, otherwise it will show 0 for the SupplierID.
is in the
supplier. It is
directly into SO
ID number of
the Item table?
the Supplier
is. That ID
of that item.
price, minimum buy
ItemID relates
Supplier table of
newsgroup reader
with newsreader"
message
number when I
Manager, there is
message
news: snipped-for-privacy@microsoft.com...
the database
just do the test item that you've been looking at. If you get results, then suppliers should be listed in the Item Properties.
Glenn Adams Tiber Creek C> Do I have to do this for each ILC? I have over 6000.
correct supplier and note the ID. That's the number you want to use in your import.as your SupplierID.
database check and re-index in SO Admin.
SupplierList! Right now we are attempting to find out who the Primary Supplier is for a particular ILC.
181 for an item purchased from your Supplier in question?imported correctly, otherwise it will show 0 for the SupplierID.
is in the
supplier. It is
directly into SO
ID number of
the Item table?
the Supplier
is. That ID
of that item.
price, minimum buy
ItemID relates
Supplier table of
newsgroup reader
with newsreader"
message
number when I
Manager, there is
message
news: snipped-for-privacy@microsoft.com...
the database
select i.itemlookupcode, i.description from item i left join supplierlist sl on i.id = supplierlist.itemID where sl.id is null
That will return all items that have no matching records in Supplier List.
Glenn Adams Tiber Creek C> just do the test item that you've been looking at. If you get results,
When I run the test, I see the column headings but nothing shows up. When I go back into SO Manager, Supplier Tab, nothing their either.
I ran the sec> select i.itemlookupcode, i.description
Sorry, here's the corrected query to display items with no supplier list entries.
select i.itemlookupcode, i.description from item i left join supplierlist sl on i.id = sl.itemID where sl.id is null
If you ran the first query and got no results, that is why there are no suppliers listed in the properties window. Even the default supplier needs an entry in SupplierList.
Here's a query that will add any suppliers that are set up in Item.SupplierID, but are not in Supplier List.
ALWAYS BACKUP YOUR DATABASE BEFORE RUNNING ANY UPDATE, INSERT OR DELETE QUERY!
INSERT INTO supplierlist (MinimumOrder, ItemID, SupplierID, Cost, ReorderNumber, MasterPackQuantity, TaxRate) SELECT MinimumOrder = 0.00, ItemID = i.id, SupplierID = s.id, Cost = i.cost, ReorderNumber = '', MasterPackQuantity = 0, TaxRate = 0.00 FROM item i inner join supplier s on i.supplierid = s.id left join supplierlist sl on i.id = sl.itemid AND s.id = sl.supplierid WHERE sl.id is null AND i.supplierid 0
Glenn Adams Tiber Creek C> When I run the test, I see the column headings but nothing shows up. When I
Thanks Glenn that worked.
Next questi> Sorry, here's the corrected query to display items with no supplier list > entries. >
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.