xTuple.com xTupleU Blog & News Customer Support

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

How do I ...?
#1

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

FROM Item

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

#2

Hi Quark,

Try this:

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

Bernard Le Jour
AS Plus Informatique Inc.

#3

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
#4

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