Hello!

I'm in the toner cartridge recycling/manufacturing business and I'm building
a software application to control our inventory. I'm having this problem and
I've tried for several months to solve it without success.

Here are my tables:

=============

CARTRIDGE_BRANDS
-----------------
PKey
Name

CARTRIDGES
-----------------
PKey
Name
Brand_FKey

CARTRIDGE_TYPES >> Virgin / Non-Virgin / Not-Ours
-----------------
PKey
Name

EMPTIES >> We deal with empty toner cartridges
-----------------
PKey
Cartridge_FKey
CartridgeType_FKey

SUPPLIERS
-----------------
PKey
Name

SUPPLIER_ITEMS >> Many suppliers can send us the same product
------------------
PKey
Name
Supplier_FKey
Empty_FKey

Special Note : I store information about quantities through another table
that holds the actual locations and quantities of all empty cartridges.

EMPTIES_STORAGES
-------------------
PKey
Qty
Empty_FKey
Supplier_FKey
Letter_FKey >> Zone - Rack - Letter ( D - 21 - F )

==============

I NEED TO FETCH A LIST OF EMPTIES
Empty_Name / Empty_Type / Qty / Supplier

Q : What should be the SQL statement to retrieve this info? I want to group
all the rows per suppliers... ??????????

Easy you say.... Well everything is good until I have the following:
SupplierEmpty 1 = HP 4500 Black (Virgin) From Supplier 1 (50 per box)
SupplierEmpty 2 = HP 4500 Black (Virgin) From Supplier 1 (100 per box)
SupplierEmpty 3 = HP 4500 Black (Non-Virgin) From Supplier 1 (50 per box)

The fault condition is when I have 2 supplier empties of the same type and
another one of a different type for the same empty. I get the Virgin empty
showing twice in the sql result.

==============================================
SELECT CartridgeBrands.CartBrand_Name,
Cartridges.Cartridge_Name,
Cartridges.CartBrand_NRI,
CartridgeBrands.CartBrand_Name,
Empties.Empty_NRI,
Empties.Cartridge_NRI,
Empties.Empty_PICode,
Empties.Empty_Desc,
Empties.CartType_NRI,
CartTypes.CartType_Name,
SupplierEmpties.Supplier_NRI,
Suppliers.Supplier_Name
FROM CartridgeBrands
INNER JOIN Cartridges ON CartridgeBrands.CartBrand_NRI =
Cartridges.CartBrand_NRI
INNER JOIN Empties ON Cartridges.Cartridge_NRI = Empties.Cartridge_NRI
INNER JOIN CartTypes ON Empties.CartType_NRI = CartTypes.CartType_NRI
INNER JOIN SupplierEmpties ON Empties.Empty_NRI = SupplierEmpties.Empty_NRI
INNER JOIN Suppliers ON SupplierEmpties.Supplier_NRI =
Suppliers.Supplier_NRI
WHERE Cartridges.CartBrand_NRI = 3 AND Empties.Cartridge_NRI = 81
ORDER BY CartridgeBrands.CartBrand_Name,
Cartridges.Cartridge_Name,
Empties.CartType_NRI,
Suppliers.Supplier_Name ASC
================================================== =====

!!!!!

You can email me for the structure of my database (JPEG File)
stephane@premiere-supplies.com

Hi

try
INNER JOIN --> LEFT OUTER JOIN ¿?

--
Ricardo Junquera
Consultor Business Intelligence

BG&S Online Consultores
Ganadora del Premio Microsoft Business Awards 2008.
Partner de Soluciones : Satisfacción de Cliente.



"Hidarisei" wrote:

> Hello!
>
> I'm in the toner cartridge recycling/manufacturing business and I'm building
> a software application to control our inventory. I'm having this problem and
> I've tried for several months to solve it without success.
>
> Here are my tables:
>
> =============
>
> CARTRIDGE_BRANDS
> -----------------
> PKey
> Name
>
> CARTRIDGES
> -----------------
> PKey
> Name
> Brand_FKey
>
> CARTRIDGE_TYPES >> Virgin / Non-Virgin / Not-Ours
> -----------------
> PKey
> Name
>
> EMPTIES >> We deal with empty toner cartridges
> -----------------
> PKey
> Cartridge_FKey
> CartridgeType_FKey
>
> SUPPLIERS
> -----------------
> PKey
> Name
>
> SUPPLIER_ITEMS >> Many suppliers can send us the same product
> ------------------
> PKey
> Name
> Supplier_FKey
> Empty_FKey
>
> Special Note : I store information about quantities through another table
> that holds the actual locations and quantities of all empty cartridges.
>
> EMPTIES_STORAGES
> -------------------
> PKey
> Qty
> Empty_FKey
> Supplier_FKey
> Letter_FKey >> Zone - Rack - Letter ( D - 21 - F )
>
> ==============
>
> I NEED TO FETCH A LIST OF EMPTIES
> Empty_Name / Empty_Type / Qty / Supplier
>
> Q : What should be the SQL statement to retrieve this info? I want to group
> all the rows per suppliers... ??????????
>
> Easy you say.... Well everything is good until I have the following:
> SupplierEmpty 1 = HP 4500 Black (Virgin) From Supplier 1 (50 per box)
> SupplierEmpty 2 = HP 4500 Black (Virgin) From Supplier 1 (100 per box)
> SupplierEmpty 3 = HP 4500 Black (Non-Virgin) From Supplier 1 (50 per box)
>
> The fault condition is when I have 2 supplier empties of the same type and
> another one of a different type for the same empty. I get the Virgin empty
> showing twice in the sql result.
>
> ==============================================
> SELECT CartridgeBrands.CartBrand_Name,
> Cartridges.Cartridge_Name,
> Cartridges.CartBrand_NRI,
> CartridgeBrands.CartBrand_Name,
> Empties.Empty_NRI,
> Empties.Cartridge_NRI,
> Empties.Empty_PICode,
> Empties.Empty_Desc,
> Empties.CartType_NRI,
> CartTypes.CartType_Name,
> SupplierEmpties.Supplier_NRI,
> Suppliers.Supplier_Name
> FROM CartridgeBrands
> INNER JOIN Cartridges ON CartridgeBrands.CartBrand_NRI =
> Cartridges.CartBrand_NRI
> INNER JOIN Empties ON Cartridges.Cartridge_NRI = Empties.Cartridge_NRI
> INNER JOIN CartTypes ON Empties.CartType_NRI = CartTypes.CartType_NRI
> INNER JOIN SupplierEmpties ON Empties.Empty_NRI = SupplierEmpties.Empty_NRI
> INNER JOIN Suppliers ON SupplierEmpties.Supplier_NRI =
> Suppliers.Supplier_NRI
> WHERE Cartridges.CartBrand_NRI = 3 AND Empties.Cartridge_NRI = 81
> ORDER BY CartridgeBrands.CartBrand_Name,
> Cartridges.Cartridge_Name,
> Empties.CartType_NRI,
> Suppliers.Supplier_Name ASC
> ================================================== =====
>
> !!!!!
>
> You can email me for the structure of my database (JPEG File)
> stephane@premiere-supplies.com