query challenge

[pser]

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

 

[Robert_Mc]

Hi

I believe that if you leave out the "where.." clause you should get what you want

Regards

Robert

 

[pser]

agree, but i want to list for that particular id. This query is going to be used as listing thign which are registered and list the different types of item available so that the user can add them as well.

 

[Robert_Mc]

I think I have what you're after now...

SELECT FreightType.FreightID, ItemType.ItemTypeID, ItemType.Type, FreightType.NumberOf
FROM ItemType LEFT JOIN FreightType ON ItemType.ItemTypeID = FreightType.ItemTypeID
WHERE (((FreightType.FreightID)=1)) OR (((FreightType.FreightID) Is Null));

Have a look-see & let me know

Regards

Robert

Ps - apologies if I haven't got all the table/field names right, though I'm sure you get the idea

 

[pser]

Seems right but it seems like i forgot to mention something, If all types are registered on another FreightID i will only get the one with selected ID and NULL. That mean i won't get all item listed.

What i'm trying to do is to get all item listed and if any of them on a particular FreightID has numberOf set i want that listed and the other should be set to nothing, but listed.

With the query you wrote i got this result:

Cardboard-------3
Fibreboard-------20

while i trying to get

Cardboard-------3
Fibreboard-------20
Item-------------
Pallet------------
Container--------


The three last item is empty.

Thanks

Paal

 

[Robert_Mc]

Try this one:

SELECT ItemType.ItemTypeID, FreightType.FreightTypeID, ItemType.Type, FreightType.NumberOf
FROM ItemType RIGHT JOIN FreightType ON ItemType.ItemTypeID = FreightType.ItemTypeID
WHERE (((FreightType.FreightTypeID)=1)) OR (((FreightType.FreightTypeID)=2)) OR (((FreightType.FreightTypeID)=3)) OR (((FreightType.FreightTypeID)=4)) OR (((FreightType.FreightTypeID) Is Null));

I think I've used FreightTypeID where you have FreightID; if so, I'm sure you can adjust accordingly...I think this one could work, fingers crossed!

Regards

Robert

 

[pser]

No i don't have a field called that. The two table i got have the following definition.

TABLE FreightType (
FREIGHTID-------------------------INT NOT NULL,
ITEMTYPEID------------------------INT NOT NULL,
NUMBEROF-------------------------INT,
FOREIGN KEY (FREIGHTID) REFERENCES Freight(FREIGHTID) ON DELETE SET NULL,
FOREIGN KEY ( ITEMTPYEID ) REFERENCES ItemType(ITEMTYPEID) ON DELETE SET NULL,
PRIMARY KEY (FREIGHTID, ITEMTYPEID)
);


TABLE ItemType (
ITEMTYPEID------------------------INT AUTO_INCREMENT NOT NULL,
Type---------------------------------CHAR(50) NOT NULL,
PRIMARY KEY ( ITEMTYPEID )
);

And i want everything from the table ItemType listed and if there are registered something in the field NumberOf in table FreightType that should be listed through foreign key.

So it should be like i wrote:

FreightID----ItemTypeID----Type--------NumberOf
1-----------------1--------------Pallet-------3
1-----------------2--------------Item--------100
NULL--------------3--------------Fibrebox----NULL
NULL--------------4--------------Container---NULL


one more thing FreightID is a foreignkey in freightType from a table freight. But i don't use that in this particular query.

tricky thing.

Paal

 

[pser]

So anyone who can give a hand?

Paal

 

[Pat Hartman]

I believe that you need to break the query into two pieces. The where clause that is applying criteria on the many-side of the join is the cause of the problem. You need to force Access to apply that criteria BEFORE it does the join and the only way to do that is to break the query into two parts.

The first query should select rows from the FreightType table with the criteria "where FreightTypeID = 1". Then join that query to the ItemType table with a left join and the criteria should be
Where FreightTypeID Is Null.

 

[pser]

I understand, i'm not sure on how to but them together. But so far it's something like this:

First to select all available items :

select *
from ItemType

Then select all which have freightID like 1

select ItemTypeID, NumberOf
from freighttype
where freightID=1

I'm unsure of how to combine them. As far i've read i'm able to create this:

select * from itemtype left join freighttype on freighttype.itemtypeid = itemtype.itemtypeid

But the where clause makes problem as you said

Paal

 

[pser]

I got solution from another forum and i was close.

Solution is:

select *
from itemtype left join freighttype
on freighttype.itemtypeid = itemtype.itemtypeid and freighttype.freightid=1