SQL help

[Jrod2541]

I have four tables: tblOrders, tblSKUItems, tblVendorItems and tblVendors.


Here's my tblOrders table:

SKU ITEM #    VendorOrder
123
124
254
542
324
786
475
521
354
254
128
009
010
704
157
858
821
724
428
129
219
481
484
411
442
754
321
 

This table exists to show the order I need to make to the vendor (VendorOrder) for each particular SKU item.

Right now the VendorOrder is blank, the point of this question is to fill it up, but I'll get to that later.



Here's the tblSKUItems table:

SKU ITEM # 
123
124
254
542
324
786
475
521
354
254
128
009
010
704
157
858
821
724
428
129
219
481
484
411
442
754
321
 

The point of this table is to contain all the SKU item #s



This is what the tblVendors table looks like:

Vendors
BAREWA
CCCNTL
HDSPLY
KISSLR
 

This table is to show all the vendors.



And lastly there's the tblVendorItems:

ID   SKU   Vend  Pri  VItem   SameDay 
1    123   MDTV   2   4987    Yes     
2    123   RYUU   1   464     Yes     
3    124   MDTV   2   5001    Yes     
4    124   RYUU   1   463     Yes     
5    254   MDTV   2   3998    Yes     
6    254   RYUU   1   085     Yes     
7    542   MDTV   2   5000    Yes     
8    542   RYUU   1   357     Yes     
9    324   MDTV   2   4304    Yes     
10   324   RYUU   1   874     Yes     
11   786   MDTV   2   4387    Yes     
12   786   RYUU   1   919     Yes     
13   475   MDTV   2   7653    Yes     
14   475   RYUU   1   898     Yes     
15   521   MDTV   2   4389    Yes     
16   521   RYUU   1   078     Yes     
17   354   MDTV   2   4020    Yes     
18   354   RYUU   1   489     Yes     
19   254   MDTV   2   4300    Yes     
20   254   RYUU   1   167     Yes     
21   128   MDTV   2   4760    Yes     
22   128   RYUU   1   168     Yes     
23   009   MDTV   2   7542    Yes     
24   009   RYUU   1   597     No      
25   010   MDTV   2   9677    Yes     
26   010   RYUU   1   598     Yes     
27   704   MDTV   0   4555    Yes     
28   704   LOWE   2   23-OH   No      
29   704   RYUU   3   101     Yes     
30   704   GVHH   1   804521  Yes     
31   157   MDTV   0   1700    Yes     
32   157   RYUU   1   416     Yes     
33   157   GVHH   2   830921  Yes     
34   858   MDTV   0   4044    Yes     
35   858   LOWE   0   23-HF   Yes     
36   858   RYUU   1   413     Yes     
37   858   GVHH   2   207871  Yes     
38   821   MDTV   0   4817    Yes     
39   821   LOWE   0   23-HJ   Yes     
40   821   RYUU   1   132     Yes     
41   821   GVHH   2   507702  Yes     
42   724   MDTV   0   8978    Yes     
43   724   LOWE   0   23-IU   Yes     
44   724   RYUU   1   533     Yes     
45   724   GVHH   2   434501  Yes     
46   428   MDTV   0   4567    Yes     
47   428   LOWE   3   23-YU   Yes     
48   428   RYUU   1   471     No      
49   428   GVHH   2   459601  Yes     
50   129   MDTV   0   5287    Yes     
51   129   LOWE   0   23-FF   Yes     
52   129   RYUU   1   503     Yes     
53   129   GVHH   2   784701  Yes     
54   219   MDTV   0   5647    Yes     
55   219   LOWE   0   42-SD   Yes     
56   219   RYUU   1   219     Yes     
57   481   MDTV   0   4456    Yes     
58   481   LOWE   0   42-BG   Yes     
59   481   RYUU   1   925     Yes     
60   484   MDTV   0   6547    Yes     
61   484   LOWE   0   42-BB   Yes     
62   484   RYUU   1   914     Yes     
63   411   MDTV   0   6457    Yes     
64   411   LOWE   0   46-EH   Yes     
65   411   RYUU   1   470     Yes     
66   442   MDTV   0   4564    Yes     
67   442   LOWE   0   31-TF   Yes     
68   442   RYUU   1   488     Yes     
69   754   MDTV   0   4898    Yes     
70   754   LOWE   0   31-AS   Yes     
71   754   RYUU   1   987     Yes     
72   321   MDTV   0   4567    Yes     
73   321   LOWE   0   31-MN   Yes     
74   321   RYUU   1   949     Yes  
 

Vend = Vendor

Pri = VendorPriority

VItem = VendorItem

SameDay = CanShipSameDay


This table exists to show all the vendors item #s and how they correlate to each sku item #.

This table also shows the priority for each vendor item. The lower the number, the higher the priority (besides 0). If it does say 0, it means that vendor doesn't have that item.

So if I need to buy SKU Item # 123, I would choose RYUU who's vendor item # is 464 because their priority number is 1 while MDTV's priority is 2.

But there's another factor involved as well, CanShipSameDay. This needs to say "Yes" or else we can't make the order. (CanShipSameDay's datatype is Yes/No)

So if I wanted to order SKU item # 009, I'd choose MDTV because, although they have a higher priority number than RYUU, RYUU's CanShipSameDay says "No" while MDTV says "Yes".




So my problem is I'm trying to create SQL code to automatically add VendorOrder values based on ttblVendorItems.

This query needs to check each SKU item in tblVendorItems and make sure the CanShipSameDay is "Yes", then choose the vendor with the lowest priority, not 0, for that SKU item.

After the query finds the vendor with that fits the description above, the query will add, in the table tblOrders under the field VendorOrder, the name of the vendor.

How do I do this?

[Seth Schrock]

I would use a subquery to get the vendor information that you are looking for. Something like the following.

SELECT SKU
, [Item #]
, Vend
FROM tblOrders As O INNER JOIN (SELECT TOP 1 Vend, SKU 
   FROM tblVendorItems
   WHERE SameDay = 'Yes'
      AND tblVendorItems.SKU = O.SKU
   ORDER BY Pri) As VI ON O.SKU = VI.SKU

I don't have a system in place where I can test this, so there might be some errors in the aliases, but it should get you on the right track.

[Rabbit]

This table also shows the priority for each vendor item. The lower the number, the higher the priority (besides 0). If it does say 0, it means that vendor doesn't have that item.

If the vendor doesn't have the item, it shouldn't be in the table. If they don't have the item, how does it even have a vendor specific item number?

What is tableOrders supposed to be?

If it's supposed to be a listing of orders placed or to be placed, it shouldn't be prepopulated with items. It should also have a unique order number. And probably an order date.

If it's supposed to be a listing of who to buy from and not necessarily of actual orders, then you don't need a table for that. You can do it with a min aggregate query on the Vendor Item table.

[twinnyfo]

Jrod,

Please see one of my previous posts on one of your previous questions.

I am in absolute agreement with Rabbit. You should not pre-populate tblOrders (in fact, you should not have that Table at all). You should find out who to buy from at run-time, as your vendors an availability may change. Otherwise, you must constantly be updating redundant data in your db, which is not recommended.