Query challenge

[pser]

I have two table like this:

Table ItemType
ItemTypeID----Type
1--------------Pallet
2--------------Item
3--------------Fibrebox
4--------------Container

Table FreightType
FreightID---ItemTypeID----Type-------NumberOf
1-------------1------------Pallet----- 3
1-------------2------------Item-------500
2-------------2------------Item-------100
2-------------1------------Pallet------1

I'm trying to create a query who list all of item in table ItemType and if there is registered Numberof in FreightType it should be listed. I have tried to created this query:

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

But i want to have a criteria like this

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

Problem then is that i only get those with id=1. So what i need is to set the criteria before the left join. Question is how that is done?

Result i'm trying to get should be like this:

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

Any tip?

Paal

[tomhath]

Try moving the freightid condition into the ON. That way it limits the join instead of limiting the final result.

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

[pser]

Perfect, thanks!!

So close but far away at the same time...

Paal 😃