Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql query: Need to add Join to query that already has left join

Posted on 2014-03-12
4
531 Views
Last Modified: 2014-03-12
My query is working perfectly. It's gleaning login, email first name and last name from wordpress tables: users and usermeta.

I need to add the field: course_progress from table: wp_wpcw_user_courses using "user_id" to join everything. My issue is that I'm already using "user_id" to join users and usermeta.

Here's my query so far using users and usermeta:

SELECT

                    
					  wp_users.user_login,
					  MAX(CASE WHEN wp_usermeta.meta_key = 'first_name' then wp_usermeta.meta_value ELSE NULL END) as first_name,
                    MAX(CASE WHEN wp_usermeta.meta_key = 'last_name' then wp_usermeta.meta_value ELSE NULL END) as last_name,
					wp_users.user_email
                   

                    FROM wp_users

                    LEFT JOIN wp_usermeta
                    ON wp_users.ID = wp_usermeta.user_id

                    GROUP BY wp_users.user_login ORDER BY 3 asc

Open in new window

0
Comment
Question by:phillystyle123
4 Comments
 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 225 total points
ID: 39924716
My issue is that I'm already using "user_id" to join users and usermeta.
So what's the problem? There's no limit on the number of times that a column can show up in JOIN clauses. You are simply applying set theory to your data. In this case, you will be joining all of the rows from each table together where there is a commonality between the user_id columns. SQL will take care of eliminating  the rows that don't fit this criteria.

Just add in the additional JOIN.
0
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 225 total points
ID: 39924720
Like this:
SELECT wp_users.user_login,
		MAX(CASE WHEN wp_usermeta.meta_key = 'first_name' then wp_usermeta.meta_value ELSE NULL END) as first_name,
		MAX(CASE WHEN wp_usermeta.meta_key = 'last_name' then wp_usermeta.meta_value ELSE NULL END) as last_name,
		wp_users.user_email
	FROM wp_users
	LEFT JOIN wp_usermeta
	ON wp_users.ID = wp_usermeta.user_id
	LEFT JOIN wp_wpcw_user_courses 
	ON wp_users.ID = wp_wpcw_user_courses.user_id
	GROUP BY wp_users.user_login ORDER BY 3 asc

Open in new window

0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 50 total points
ID: 39924721
You can use the same column to join multiple tables, I am assuming there is a user_id column in the wp_wpcw_user_courses table :

SELECT

                    
					  wp_users.user_login,
					  MAX(CASE WHEN wp_usermeta.meta_key = 'first_name' then wp_usermeta.meta_value ELSE NULL END) as first_name,
                    MAX(CASE WHEN wp_usermeta.meta_key = 'last_name' then wp_usermeta.meta_value ELSE NULL END) as last_name,
					wp_users.user_email
                   

                    FROM wp_users

                    LEFT JOIN wp_usermeta
                    ON wp_users.ID = wp_usermeta.user_id

                    LEFT JOIN wp_wpcw_user_courses 
                    ON wp_users.ID = wp_wpcw_user_courses.user_id

                    GROUP BY wp_users.user_login ORDER BY 3 asc 

Open in new window

0
 

Author Closing Comment

by:phillystyle123
ID: 39924770
thanks all -works perfectly - much appreciated
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question