Search Query that Includes Custom Table

Question

I have a WordPress website that grabs product data from a custom table. I would like to be able to also search from fields in the custom table. Could someone provide a sample query of what this would look like? I cannot join the custom table to the posts table because the custom table has data that is not associated with any other database tables (unless I am misunderstanding joins). The end goal is to show all posts and custom table data that match the specified "s" variables data.

Thanks!

UPDATE

Here is the requested table structure of the custom table:

Just to clarify, I am trying to modify the WordPress search so that it searches the posts table using a like query. I also need it to return results that match from the custom table as well. I will need it to search the subarticle and Article fields of the custom table.

Answer 1

I managed to solve this by taking s_ha_dum's suggestion to use a UNION. It is as follows:

(SELECT ID, post_status, post_title, post_excerpt, post_content FROM wp_posts WHERE ((`post_title` LIKE '%diamond%') OR (`post_excerpt` LIKE '%diamond%') OR (`post_content` LIKE '%diamond%')) AND (`post_status` = 'publish' )) UNION (SELECT ref, StoreID, Article, subarticle, description FROM wp_hwproducts WHERE (`article` LIKE '%diamond%') OR (`subarticle` LIKE '%diamond%') OR (`description` LIKE '%diamond%')) ORDER BY `post_status` DESC;