I'm a little confused about how the tables are related. Can you provide some sample data and an example of how you would like the results to show?

Each user is responsible for a number of items (itemnos or inventory no’s), each item can be either a living object (ie a gerbil) or an object (a table) but registered with a unique inventory number, maybe I should have named itemnos inventorynos instead.

I would like to display is list of items (inventorynos) that are associated with the user displayed as

Itemno - itemtype1.item - itemtype1.size - itemtype1.etc
Itemno - itemtype2.name - itemtype2.dob - itemtype2.etc
And so on.

In output:
Item 2 - table - big - etc.
Item 7 - Ethel Henkinsop III - 20 May 2013 - etc.

I think that maybe a join table Join_user_itemnos_itemtype1_itemtype2 would work but I am unsure if it's the best way.

Thanks

If you JOIN, you're going to get items on the same row. What you're looking for is more likely a UNION.

The problem you're facing is that the fields are different between the tables you're trying to UNION. You need to have the same number of fields for each SELECT in the UNION, so if you've got just one field for etc for each table, you can make it work.

However, itemtype1.size and itemtype2.dob are (presumably) different data types- I'm guessing VARCHAR for size and DATE for dob. You'll have to kludge things by converting dob to a VARCHAR on the fly.

I still don't see how the tables are related between user and items. You need a lookup table that maps an item type (specifying if it's in the itemtyp1 or itemtype2 table) and Itemno with a user's id.

items is a physical tag that they attach to things. Like I said, I should have called it an inventoryno or tagno instead. Each person is assigned responsibility for items in their area, so the number could be anything and attached to anything.

They have quite a lot of animals wandering around as well as objects so the itemtype tables don’t have the same structure (not even similar), and I need to find a way to attach to both.

I think possibly two queries based on itemtype with two join tables. I will go for a walk and think it through, maybe down the pub.

Thanks for your help.

If the item types are so dissimilar, I think it's going to be better to do 2 queries.

I have decided to split it out into two sections, livestock and items.

Thanks for helping me clarify my thoughts.