[Resolved] SQL Query to get content from 2 custom post type with a relationship.

[Author]

Posts

[georgey]

I'd like to write a SQL query to extract content from 2 custom post types(Chapters and Members) that have a relationship

Chapters and Members
1 chapter can be assigned to one member
infinite members can be assigned to one chapter

I've looked at the various tables but I'm not able to determine which tables contain the relationship info to do the necessary Join in my SQL statement. Would you please provide some insight or additional documentation on how to accomplish my SQL Query

[Minesh]

Hello. Thank you for contacting the Toolset support.

We offer post relationship API function to get related posts:
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/

In addition to that if you want to use core WP_Query, please check the following Doc:
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/how-to-migrate-your-site-to-new-post-relationships/#wp_query-argument-for-querying-by-related-posts

Please let me know if that helps.

[georgey]

While this information is helpful, it doesn't solve my problem. do you have schema documentation on the database tables you are using for relationships? I'm not using php or your api, I'm writing SQL.

[Minesh]

Please check the following screenshot for schema: hidden link

The relationship with content is stored using the following database tables:
- {db-prefix}_toolset_connected_elements
- {db-prefix}_toolset_relationships
- {db-prefix}_toolset_associations
- {db-prefix}_toolset_type_sets
- {db-prefix}_posts

I hope this helps.