Need help with joining tables (MySQL)

Hi all

I am not very advanced in database scripting/programming, but have a small issue:
This code is created for a wordpress plugin widget, <prefix> is the table prefix as set in wordpress.

For what I need to do, I need to query 4 tables (<prefix>wp_eMember_members_tbl, <prefix>wp_eStore_customer_tbl, <prefix>users, <prefix>comments).
The join areas between the tables are:

  • <prefix>wp_eMember_members_tbl.user_name = <prefix>wp_eStore_customer_tbl.member_username
  • <prefix>wp_eMember_members_tbl.user_name = <prefix>users.user_login
  • <prefix>users.ID = <prefix>comments.user_id

My idea is:

  1. Select a random member from <prefix>wp_eMember_members_tbl
  2. Using the ‘user_name’ and ‘member_id’ get the Comment Count and the Sales Count (from the <prefix>comments.user_id and <prefix>wp_eStore_customer_tbl.member_username respectively)
  3. The information like ‘since’ and ‘country’ are from the

The code I so far have is:


SELECT
	  <prefix>wp_eMember_members_tbl.user_name AS username
	, <prefix>wp_eMember_members_tbl.member_id AS userid
	, <prefix>wp_eMember_members_tbl.member_since AS since
	, <prefix>wp_eMember_members_tbl.country AS country
	, COUNT(<prefix>wp_eStore_customer_tbl.member_username) AS sales
	, COUNT(<prefix>comments.user_id) AS comments
FROM
	<prefix>wp_eMember_members_tbl
		INNER JOIN <prefix>wp_eStore_customer_tbl
			ON <prefix>wp_eMember_members_tbl.user_name = <prefix>wp_eStore_customer_tbl.member_username
		INNER JOIN <prefix>users
			ON <prefix>wp_eMember_members_tbl.user_name = <prefix>users.user_login
		INNER JOIN <prefix>comments
			ON <prefix>users.ID = <prefix>comments.user_id
GROUP BY
	<prefix>wp_eMember_members_tbl.user_name

This is however not returning any results.

The tables are set up like (* and ^ mark the Join Areas):

<prefix>wp_eMember_members_tbl
	member_id
	user_name^
	first_name
	last_name
	password
	member_since
	membership_level
	more_membership_levels
	account_state
	last_accessed
	last_accessed_from_ip
	email
	phone
	address_street
	address_city
	address_zipcode
	country
	gender
	referrer
	extra_info
	reg_code
	subscription_starts
	initial_membership_level
	txn_id
	subscr_id
	company_name
	flags

<prefix>comments
	comment_ID
	comment_post_ID
	comment_author
	comment_author_email
	comment_author_url
	comment_author_IP
	comment_date
	comment_date_gmt
	comment_content
	comment_karma
	comment_approved
	comment_agent
	comment_type
	comment_parent
	user_id*

<prefix>users
	ID*
	user_login^
	user_pass
	user_nicename
	user_email
	user_url
	user_registered
	user_activation_key
	user_status
	display_name

<prefix>wp_eStore_customer_tbl
	id
	first_name
	last_name
	email_address
	purchased_product_id
	txn_id
	date
	sale_amount
	coupon_code_used
	member_username^
	product_name
	address
	phone
	subscr_id
	purchase_qty
	ipaddress
	status

Any help greatly appreciated.
Regards
Jacotheron

If no rows are returned then

  • you might be joining on the wrong columns?
    or
  • you don’t always have rows in all four tables for each user: try LEFT OUTER JOIN’s instead of INNER JOIN’s.

Hi guido2004
Thank you very much.
The Left Outer Join did the trick. I decided against the random after hours of trying (and searching) as nothing wanted to consistently give me a result (that is not empty). Now someone have to pick the member to appear in the widget.

Again thank you very much.
Jacotheron