Tricky SQL joining tables question 1

[majorbiff]

I have 3 tables that look sort of like this (only the relevant bits):
table 1: mainitems
itemid name

table 2: itemparts
id testID iname type count

table 3: category
id cat

items.testID is a reference to tests.itemID
items.type is a reference to category.id
An item ('a test') in mainitems can contain multiple itemparts ('an item') - hence the reference to testID in itemparts.
Each 'item' has a particular type -> cons or noncons.
Different types could be added later so I dumped the types into category and have a refence to category.id via itemparts.type...

category table:
id cat
0 cons
1 noncons
Therefore, for each row in itemparts, there is a value either 0 or 1 specifying the type.

I have combined the tables so I can bring up all the data that I need with this:

SELECT itemparts.id, testID, iname, itemparts.count, mainitems.name, category.cat FROM itemparts, mainitems, category WHERE itemparts.id = mainitems.itemID AND itemparts.type = category.id

It works well, however I need to limit the results to the number of 'items' as stored under itemparts.count.
I could add:

"AND itemparts.count <= x"

however I want to specify a different count for different types.

For example; itemparts.count = 10 if itemparts.type = 0 and itemparts.count = 1 if itemparts.type = 1.

Any ideas?

Alchemy is easy with Perl!
s/lead/gold/g;

 

[r937]

select itemparts.id
     , testID
     , iname
     , itemparts.count
     , mainitems.name
     , category.cat 
  from itemparts
inner
  join mainitems
    on mainitems.itemID  = itemparts.id
inner
  join category 
    on category.id = itemparts.type
 where itemparts.type = 0 and itemparts.count = 10 
    or itemparts.type = 1 and itemparts.count = 1

r937.com | rudy.ca

 

[majorbiff]

I tinkered with inner join but couldn't quite get it to work properly. Your solution works perfectly, thanks.

Alchemy is easy with Perl!
s/lead/gold/g;