Hi, Kurt

Thanks for reaching out to us

This is an example of a SQL JOIN between the Amelia Services table and Amelia Extras table.

We will choose all the rows that match Service ID  and Extras ID from the respective tables :

SELECT wp_amelia_services.name AS 'service name', 
wp_amelia_extras.id, 
wp_amelia_extras.name AS 'extra name', 
wp_amelia_extras.description,
wp_amelia_extras.price
FROM wp_amelia_extras 
JOIN wp_amelia_services ON wp_amelia_extras.id=wp_amelia_services.id

Of course, you can rearrange the order of columns as you need them, and add/remove any of them.

This is just an example of how you can join the wp_amelia_services  table in order to pull the service name column,

and the other columns for Extras are pulled from the wp_amelia_extras table.

This is how my wpDataTable looks :

As you can see, when two or more columns from the JOIN have the same header name,

   in that case, our SQL parser pulls just one of them, so if we wish to pull them all,  we have to use aliases "AS" to create different header names, then wpDataTables will get all the data. 

Let me know if that helped. Thank you

Hi, Kurt

And i almost forgot,  when we use JOINs like that, there is a chance that our SQL Parser won't be able to process filtering with server-side processing.

On my particular table, filtering did not work at first, so i had to make an SQL View in my database,  which i later called to make a new SQL Table and now filtering works.

Just to know for the future, i will point out some limitations to be aware of.

Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types

SELECT, INSERT, UPDATE, DELETE, REPLACE, RENAME, SHOW, SET, DROP, CREATE INDEX, CREATE TABLE, EXPLAIN and DESCRIBE.

Some of them are disabled for security reasons.

Filtering, sorting, and search may not work properly if you include:

• Accent graves ( ` ) around the table name
• JOIN functions
• UNION functions
• CONCAT functions
• sub-queries

-

You can then try preparing a MySQL view (which will return the data that you need, call it e.g. “view1” and then build a wpDataTables based on a simple query like "SELECT * FROM view1″.

 If you need help with that, you can see our video, where we show an example of using View in our plugin.

-

Additionally, you can check out my screen recording on how I did that with my table to fix the filtering.

Let me know how it goes, i hope that helps!

Thank you