-2

I have two tables (items and sub_items). Both tables have an asset_number column. I need a query that will let me add a new item only if the given asset_number is not present in either of the two tables.

RAS
  • 8,100
  • 16
  • 64
  • 86
sgspragg
  • 19
  • 6

2 Answers2

0

Can you be more specific on the fields?

Does your sub_items table have itemsId?

If yes, I think you can join those two tables and search for the new item?

Something like this:

SELECT I.item_id AS item_items_id, SI.item_id AS subitem_items_id FROM items I
INNER JOIN sub_items SI ON I.item_id = SI.item_id AND I.asset_number = SI.asset_number
WHERE I.item_id = -- if your new item has an item_id you can search it

If the new item doesn't have an ID, then maybe you can search by using a item name if there is a column?

OHHO
  • 143
  • 2
  • 9
  • Thanks for your response. Rather than list all the fields (as most are irrelevant for the query), the items table has item_id and asset_number. The sub_items table has sub_item_id, item_id and asset_number. – sgspragg Dec 05 '15 at 00:11
  • if that is the case, why not using INNER JOIN? – OHHO Dec 05 '15 at 00:13
  • Which field would be best to INNER JOIN with? I basically want asset_number to be unique across both tables. This is the query I tried earlier, but it throws an error: SELECT item_id FROM items AS i INNER JOIN sub_items AS s USING (asset_number) WHERE asset_number=? – sgspragg Dec 05 '15 at 00:15
0

You can search for your item in both tables at the same time, using a emulated outer join, if it returns nothing you can proceed the inserts. Refer to these link:

Full Outer Join in MySQL

Community
  • 1
  • 1
Flávio Filho
  • 475
  • 4
  • 14
  • I'm not sure I have explained properly. It's not the item that I'm querying. I need to be able to add a new item to the table, but only if the asset_number provided for the new item does not appear in either of the two tables. This therefore makes asset numbers unique across both tables. – sgspragg Dec 05 '15 at 00:37
  • That's ok, you can create a unique index for the fields asset_number, this for itself will guaranty that at least in each table the item can't be repeated. Before you insert a new item, you run a query, as described in my answer, to be sure that there are no other item with the same asset_number on any of the tables, then you proceed the insert. – Flávio Filho Dec 05 '15 at 02:27