grabbing odd results, probably join issue

[aebstract]

SELECT customfieldview.info, customfieldview.recordid, product.partid, soitem.totalprice AS TOTALPRICE, soitem.productid, product.id

FROM customfieldview
JOIN product ON customfieldview.recordid = product.partid
JOIN soitem ON soitem.productid = product.id
JOIN so ON so.id = soitem.soid

WHERE customfieldview.info = 'Equipment'
AND so.dateissued >= '2010-2-1'
AND so.dateissued <= '2010-2-28'

ORDER BY product.id

 

The db is slightly sporatic, and may need to be.. it's an inventory control setup. Anyway, I'm basically trying to get a total of "Equipment" sales for the month of February. Here's how it works:

 

Products get put on to sales orders.

Every product has a parent part. Part's may have several products.

Inside a part is a custom field called expense, it can be 1 of several options. We're looking at Expense 'equipment' right now.

I've got a part number B-FP-5 that is showing up on 5 lines with the above code, when I search through physical orders, it's only one 1 order, and it isn't in February.

 

Need any more info? Just let me know.

[aebstract]

Okay, I got a list of results, and they aren't all associated with "Equipment". So I know for a fact it's the way it's joined together. Results to the above query will be shown below. I'm going to try and explain what I need to do a little better:

 

Should take all orders in Feb. Grab only soitems that have a part that has an expense of Equipment. To check that you have to grab the part that has the reference to the product in the soitem, to get the expense.

 

 

Everything with a negative value is actually an expense of "Discount". The first 8 results are actually "Purchased".

I'm sorry the columns aren't labeled. They go from left to right:

Info, Recordid, Partid, Totalprice, Productid, ID

 

Thanks!

[aebstract]

bump, if I need to rewrite this with better structure just let me know. I gotta try and figure this out

[XeNoMoRpH1030]

Can you post some of the relevant structures for Customfieldview, product, soitem, and so? With that, I'm sure it would be easier to figure out.

[aebstract]

Can you post some of the relevant structures for Customfieldview, product, soitem, and so? With that, I'm sure it would be easier to figure out.

 

 

Customfieldview: recordid (=part.id), info - this is a view

Product: id, partid (=part.id)

Soitem: id, productid (=product.id), soid (=so.id), totalprice

So: id, dateissued

 

I'm needing to get a total of the soitem.totalprice column, where so.dateissed = in february 2010 (ill change this for each time I need it) AND customfieldview.info = 'Equipment'.

 

Hope this clears it a little bit more, it's not too difficult, I just think I'm doing my joins in the wrong order or I should be using something other than just a plain JOIN.

 

 

Note: When you do the query I mentioned in the first post, it like I said will return some parts that have not been set as "equipment" but rather set as "discount". I have to take the recordid and physically check them to see that they are, but I have on a few and it's grabbing mixed results. Again, I think the issue lies in my joins.