I've been working on this query which i can't seem to get right. I have two table ItemType (2 fields: ItemTypeID, Type). Then FreightType (3 fields: FreightTypeID, ItemTypeID, NumberOf).
FreightTypeId is a unique freight which can containing several items
Table ItemType
ItemTypeID----Type
1--------------Pallet
2--------------Item
3--------------Fibrebox
4--------------Container
Table FreightType
FreightTypeID----ItemTypeID----Type-------NumberOf
1-----------------1--------------Pallet------ 3
1-----------------2--------------Item-------500
2-----------------2--------------Item-------100
2-----------------1--------------Pallet-------1
I have so far created this query :
SELECT FreightType.FreightID, ItemType.ItemTypeID, ItemType.Type, FreightType.NumberOf
FROM ItemType left JOIN FreightType ON ItemType.ITEMTYPEID = FreightType.ITEMTYPEID
WHERE FreightID=1;
The result i then get is this
FreightTypeID----ItemTypeID----Type--------NumberOf
1-----------------1-----------------Pallet----3
1-----------------2-----------------Item-----100
But i don't get what i'm looking for. I want to list everything in table ItemType and if there are nothing registered on other Itemtypes then FreightID and NumberOf should be NULL
Like this:
FreightTypeID----ItemTypeID----Type--------NumberOf
1-----------------1--------------Pallet-------3
1-----------------2--------------Item--------100
NULL--------------3--------------Fibrebox----NULL
NULL--------------4--------------Container---NULL
So what i want is to list everything in table ItemType and if there are registered NumberOf in table FreightType then i want it to be listed otherwise it should be listed and set to NULL.
I guess i'm close to the solution, but i'm stuck.
Paal
FreightTypeId is a unique freight which can containing several items
Table ItemType
ItemTypeID----Type
1--------------Pallet
2--------------Item
3--------------Fibrebox
4--------------Container
Table FreightType
FreightTypeID----ItemTypeID----Type-------NumberOf
1-----------------1--------------Pallet------ 3
1-----------------2--------------Item-------500
2-----------------2--------------Item-------100
2-----------------1--------------Pallet-------1
I have so far created this query :
SELECT FreightType.FreightID, ItemType.ItemTypeID, ItemType.Type, FreightType.NumberOf
FROM ItemType left JOIN FreightType ON ItemType.ITEMTYPEID = FreightType.ITEMTYPEID
WHERE FreightID=1;
The result i then get is this
FreightTypeID----ItemTypeID----Type--------NumberOf
1-----------------1-----------------Pallet----3
1-----------------2-----------------Item-----100
But i don't get what i'm looking for. I want to list everything in table ItemType and if there are nothing registered on other Itemtypes then FreightID and NumberOf should be NULL
Like this:
FreightTypeID----ItemTypeID----Type--------NumberOf
1-----------------1--------------Pallet-------3
1-----------------2--------------Item--------100
NULL--------------3--------------Fibrebox----NULL
NULL--------------4--------------Container---NULL
So what i want is to list everything in table ItemType and if there are registered NumberOf in table FreightType then i want it to be listed otherwise it should be listed and set to NULL.
I guess i'm close to the solution, but i'm stuck.
Paal