Is there a way to do this without using a Query Loop?

[Failing_Solutions]

I have 2 tables, and I want to get a Total Qty Available

 

Here is an Example of what I'm trying to deal with

 

TableA is our part_number with associated supplier part numbers

TableB is all our part_numbers and suppliers part numbers with the stock totals

 

I need to Search TableB for both our part number and supplier parts then add them up.

 

Which I can do on a individual basis with sql and the IN statement, but that would require me to make a PHP loop to get Table A data, then loop through it. This I'm told I should avoid.

 

Here is the data (Examples)

TABLE_A Our Part Number Associated with supplier part numbers

model

supplier_model1

supplier_model2

supplier_model3

supplier_model4

Green-Widget

.25X9X13.25

AS001V14

Blue-Wiget

.375BNT

AS001-K6

AS001-4200-70

AS001-75N

Brown-Widget

Yellow-Widget

.500BNK

002-273I

Black-Widget

.750BNW

002-282I

Neon-r-Wiedget

AS001-75N

003-215

AS001-9009-75

AS001-75F

 

 

TABLE_B Everybody's Inventory

part_numbersstock

.25X9X13.25178

.375BNT0

.500BNK7200

.750BNW9234

AS001-75N0

AS001V140

AS001-K60

002-273I0

002-282I0

003-21515

AS001-4200-7010

AS001-9009-752

AS001-75N0

AS001-75F3

Green-Widget2

 

Here is the caveats not every Part Number from Table A will be in Table B. I don't think I can use any JOIN statement because there is no way I know of to use OR in the joins.

 

The best solution I have come up with is to run a SQL statement like this

 

SELECT part_numbers, SUM(stock) FROM TABLE_B
WHERE part_numbers IN('.375BNT','AS001-K6','AS001-4200-70','AS001-75N')

Results: AS001-4200-70 10

 

Which gives correct results, but to process this data I would then have to first  Select Column 1-5 of TABLE_A then foreach $row Query TABLE_B to get the stock then save it.

 

 

I've been racking my brain on this all weekend but I can't seem to get a solution that doesn't require a loop. TABLE_A has 8,000'ish rows, and TABLE_B has 15,000'ish rows so this would be a heck of a loop.

 

Any help thoughts, suggestions would be appreciated.

 

[Psycho]

I could provide you a solution to what you have now, but . . .

TableA is our part_number with associated supplier part numbers

TableB is all our part_numbers and suppliers part numbers with the stock totals

. . . you need to change that design. Especially the fields for supplier_model1, supplier_model2, etc. That is unsustainable. I think I know why you did that.

 

I assume the reason you are doing that is that you are getting the same parts from different suppliers who have their own part numbers. If that is the case you need two tables to "describe" the parts. I would have something like this:

 

Table: parts

This table would contain fields to describe the common features of the part. It would contain only YOUR part number as well as an auto-increment ID field.

 

Table: parts_suppliers

This table would contain a record for every part that each supplier provides. So, if part #1 has 3 suppliers there would be three records. The table would only need the auto-increment ID field from the parts table, the supplier part number and a supplier ID (ideally there would be a separate table to list the suppliers). This table should also use an auto-increment ID field.

 

Now, as for stock. If you need to store the inventory by the supplier then you just need to store the ID from the parts_suppliers table along with a quantity.

 

Using the above structure you could get the total quantity for all parts (per YOUR part number) using a query such as:

SELECT parts.part_no, parts.name, SUM(inventory.count) as inventory
FROM parts
LEFT JOIN parts_suppliers
  ON parts.part_id = parts_suppliers.part_id
LEFT JOIN inventory
  ON parts_suppliers.part_sup_id = inventory.part_sup_id
GROUP BY parts.part_id

[Failing_Solutions]

That does make sense, I'll see about setting up the second table and see where I can get.

 

Thank you, I'll update this later today with my results.