CODEWITHSUNDEEP
phpsqlwordpress
tomwgf
tomwgf

Reputation: 1

How do I SELECT from 3 different Wordpress Tables to find result?

I have a SELECT statement that pulls data from one table:

SELECT user_id, user_email, product_id, download_count  
FROM l3x_woocommerce_downloadable_product_permissions

but using the user_id I need to find the group_id from another table

and using the group_id then get the group name (name) from a third table.

so have this: 

SELECT r1.user_id, r1.user_email, r1.product_id, r1.download_count, r2.group_id, r3.name 
FROM l3x_woocommerce_downloadable_product_permissions r1
, l3x_groups_user_group r2
, l3x_groups_group r3

but i dont think the 2nd and 3rd table data is usable as it is not associated with user id.

How do i join (JOIN?) the 3 tables together to get group name?

Upvotes: 0

Views: 49

Answers (3)

tomwgf
tomwgf

Reputation: 1

Thank you for all your help. This seems to be working:

SELECT r1.user_id, r1.product_id, r1.download_count, r2.group_id, r3.name
FROM l3x_woocommerce_downloadable_product_permissions r1
JOIN l3x_groups_user_group r2 ON r1.user_id = r2.user_id
JOIN l3x_groups_group r3 ON r2.group_id = r3.group_id
WHERE r1.download_count > 0  AND r2.group_id != 1

Upvotes: 0

Dylan Cross
Dylan Cross

Reputation: 609

You'll have to pick a field in each table that is shared.

I'd assume r1.group_id=r2.group_id and r2.name=r3.name. In your case, the fields in r1/r2 may have different names than in r2/r3s. You also might have to join using a different field than these. I don't know without knowing all the fields in each table.

You'll then JOIN ON those common fields.

So you'll use somthing like

SELECT r1.user_id, r1.user_email, r1.product_id, r1.download_count, r2.group_id, r3.name 
FROM ((l3x_woocommerce_downloadable_product_permissions r1
LEFT JOIN l3x_groups_user_group r2 ON r1.group_id=r2.group_id)
LEFT JOIN l3x_groups_group r3 ON r2.name=r3.name)

If you give a list of the fields in all 3 tables, I can give a more specific answer, but this is the form of the query you need.

Upvotes: 0

Patrick Williams
Patrick Williams

Reputation: 21

if the tables are set up so that r2 contains user_id and associated group_id and r3 is set up similarly you can

use join 

SELECT r1.user_id, r1.user_email, r1.product_id, r1.download_count, r2.group_id, r3.name, r3.group_name 
FROM l3x_woocommerce_downloadable_product_permissions r1   
JOIN l3x_groups_user_group r2
     ON r1.user_id = r2.user_id 
JOIN l3x_groups_group r3 
     ON r2.group_id = r3.group_name

Upvotes: 1

Related Questions