Exporting package names and stock numbers

[Meddysong]

We don't just sell our goods through our site. People may attend our courses and buy directly from the shelves, or when we've got a stand at a conference, people pick up a book and pay with a Paypal Here card reader.

The result is that our stock numbers as indicated within Commerce aren't necessarily accurate. What I'd like to do is run a query to see what Commerce thinks we have, then I will know what to manually update once I've checked what we have on the shelves.

This seems a bit challenging. I have two languages on my site so I have to join at least two tables: one (nexus_packages) to export the ID, stock number etc, and another (core_sys_lang_words) to get the name of the product.

I would normally be able to work this out but there's an additional problem: in my WHERE clause for core_sys_lang_words I would normally check that there was a p_id from nexus_packages; but the id is embedded within the word_key, so I can't just look for the p_id. (For example, the product with the p_id 33 would correspond to the word_key nexus_package_33.)

Does anybody have any insight into how I might solve this?

[newbie LAC]

SELECT p_id, p_stock, word_custom FROM nexus_packages LEFT JOIN core_sys_lang_words ON lang_id=1 AND word_key=CONCAT('nexus_package_', nexus_packages.p_id);

 

[Meddysong]

Perfect -- thanks!