xTuple.com xTupleU Blog & News Customer Support

How to use Inner join to find Items without assigned tax types

I’m trying to generate a SQL statement for xTuple 4.10.1 that will display the tax type assigned to items

I know I need to pull from the tables ‘item’, ‘itemtax’ ,and ‘taxype’

The statement so far looks like

Select Item_id, item_number, item_descrip1, item_descrip2, itemtax_id, itemtax_taxtype_id


join itemtax ON (itemtax_item_id=item_id)

How do I join a 3rd table with INNERJOIN and where do do I need to put it considering I need to add
taxtype_id , taxtype_name

Hi Quark,

Try this:

SELECT item_id, item_number, item_descrip1, item_descrip2, itemtax_id, itemtax_taxtype_id, taxtype_name
JOIN itemtax ON itemtax_item_id=item_id
JOIN taxtype ON taxtype_id = itemtax_taxtype_id

Bernard Le Jour
AS Plus Informatique Inc.

The above suggestion will only show items with an item tax type.

The below SQL will show all items with no tax type assigned:

select item_number
from item
left outer join itemtax on itemtax_item_id=item_id
where itemtax_id is null
order by item_number

Thank you Dave,

got the list I needed, I forgot that IS NULL was a thing.

Sorry If I didn’t make the need to find Items without tax types clearer initially Bernard