Need help writing mysql query that joins data from 3 different tables

I need some help writing a mysql query that joins data from 4 different tables.

This is what I currently have (where I'm retrieving and linking data from 2 different tables using "LEFT JOIN"):

SELECT 
seo_clientrequests.id,
seo_clientrequests.client_id,
seo_clientrequests.subject,
seo_clientrequests.private,
seo_clientrequests.project_type,
seo_clientrequests.status,
seo_clientrequests.submitted_date,
seo_clientrequests.last_updated,
seo_clientrequests.due_date,
users.companyname,
users.usertype,
users.whitelabel_company_id 
FROM seo_clientrequests 
LEFT JOIN users 
ON seo_clientrequests.client_id = users.id 
WHERE users.status = '1'

Open in new window


How would I update this query so that it also retrieves data from a 3rd table named "seo_projects" ?

Here are the columns that are in my seo_projects table:

seo_projects.ID,
seo_projects.client_id,
seo_projects.package_id,
seo_projects.project_start_date,
seo_projects.project_due_date,
seo_projects.project_url,
seo_projects.project_desc,
seo_projects.percent_complete,
seo_projects.waiting_on_reply,
seo_projects.email_chain_exults_client,
seo_projects.email_chain_wl_client,
seo_projects.email_chain_wl,
seo_projects.last_updated,
seo_projects.entered

The LEFT JOIN statement for this would be pretty much identical to the one I'm already using, though I can't seem to figure out how to update the query with a 2nd LEFT JOIN statement, .. or if that's even the correct approach:

LEFT JOIN users ON seo_projects.client_id = users.id

Open in new window



The goal would be for me to retrieve a DISTINCT listing of rows for both the "seo_clientrequests" & "seo_projects" tables (ie: if there are 5 rows in the seo_client requests table and 4 rows in the seo_projects table .. my query should return 10 rows), and to link both of them to the "users" table (so I can retrieve the companyname, usertype, and whitelabel_company_id values).

Please advise.

Thanks,
- Yvan
egoselfaxisAsked:
Who is Participating?
 
Barry62Commented:
So did I help in any way?
0
 
Barry62Commented:
SELECT 
seo_clientrequests.id,
seo_clientrequests.client_id,
seo_clientrequests.subject,
seo_clientrequests.private,
seo_clientrequests.project_type,
seo_clientrequests.status,
seo_clientrequests.submitted_date,
seo_clientrequests.last_updated,
seo_clientrequests.due_date,
users.companyname,
users.usertype,
users.whitelabel_company_id 
seo_projects.ID,
seo_projects.package_id,
seo_projects.project_start_date,
seo_projects.project_due_date,
seo_projects.project_url,
seo_projects.project_desc,
seo_projects.percent_complete,
seo_projects.waiting_on_reply,
seo_projects.email_chain_exults_client,
seo_projects.email_chain_wl_client,
seo_projects.email_chain_wl,
seo_projects.last_updated,
seo_projects.entered
FROM seo_clientrequests 
LEFT JOIN users 
ON seo_clientrequests.client_id = users.id 
LEFT JOIN seo_projects
ON seo_clientrequests.id = seo_projects.client_id
WHERE users.status = '1'
                                  

Open in new window

0
 
egoselfaxisAuthor Commented:
Nope .. that doesn't work.  

First, .. I had to add in a missing comma, .. and then I tried changing ...

"ON seo_clientrequests.id = seo_projects.client_id"

to ...

"ON seo_clientrequests.client_id = seo_projects.client_id"

... but I am still returning the incorrect number of rows.  

The seo_clientrequests table contains 17 rows, .. and the seo_projects table contains 3 rows, .. but for some reason the query is returning 27 rows.

- yg
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Barry62Commented:
OK,

start the query with "SELECT DISTINCT" and change the left join of the users table to an inner join.
0
 
egoselfaxisAuthor Commented:
When I apply the revisions to you've suggested, I return 37 rows.

SELECT DISTINCT 
seo_clientrequests.id,
seo_clientrequests.client_id,
seo_clientrequests.subject,
seo_clientrequests.private,
seo_clientrequests.project_type,
seo_clientrequests.status,
seo_clientrequests.submitted_date,
seo_clientrequests.last_updated,
seo_clientrequests.due_date,
users.companyname,
users.usertype,
users.whitelabel_company_id, 
seo_projects.ID,
seo_projects.package_id,
seo_projects.project_start_date,
seo_projects.project_due_date,
seo_projects.project_url,
seo_projects.project_desc,
seo_projects.percent_complete,
seo_projects.waiting_on_reply,
seo_projects.email_chain_exults_client,
seo_projects.email_chain_wl_client,
seo_projects.email_chain_wl,
seo_projects.last_updated,
seo_projects.entered
FROM seo_clientrequests 
INNER JOIN users 
ON seo_clientrequests.client_id = users.id 
LEFT JOIN seo_projects
ON seo_clientrequests.client_id = seo_projects.client_id
WHERE users.status = '1'

Open in new window

0
 
Barry62Commented:
Sorry, I meant to also say switch around seo_projects and users

LEFT JOIN seo_projects
ON seo_clientrequests.client_id = seo_projects.client_id
INNER JOIN users 
ON seo_clientrequests.client_id = users.id 
WHERE users.status = '1'

Open in new window

0
 
egoselfaxisAuthor Commented:
That still returns 37 rows.
0
 
Barry62Commented:
this is tricky.  Try a group by clause at the end:

GROUP BY seo_clientrequests.id,seo_projects.ID

Open in new window

0
 
egoselfaxisAuthor Commented:
No difference .. still getting 37 rows.
0
 
Barry62Commented:
could you give me a sample of your data?  I can run some tests.
0
 
egoselfaxisAuthor Commented:
I actually decided on using the "UNION ALL" approach :

SELECT  
users.companyname,
users.usertype,
users.whitelabel_company_id,
seo_clientrequests.ID AS theid,
seo_clientrequests.project_type AS the_project_type,
seo_clientrequests.due_date AS the_due_date,
seo_clientrequests.status AS status_percent_complete,
seo_clientrequests.subject AS subject_package_id,
seo_clientrequests.private AS private_waiting_on_reply,
seo_clientrequests.client_id,
seo_clientrequests.last_updated
FROM seo_clientrequests 
LEFT JOIN users ON seo_clientrequests.client_id = users.id 
WHERE users.status = '1' 
ORDER BY seo_clientrequests.last_updated DESC) 
UNION ALL 
(SELECT 
users.companyname,
users.usertype,
users.whitelabel_company_id,
seo_projects.ID AS theid,
seo_projects.project_url AS the_project_type,
seo_projects.project_due_date AS the_due_date,
seo_projects.percent_complete AS status_percent_complete,
seo_projects.package_id AS subject_package_id,
seo_projects.waiting_on_reply AS private_waiting_on_reply,
seo_projects.client_id,
seo_projects.last_updated
FROM seo_projects 
LEFT JOIN users ON seo_projects.client_id = users.id 
WHERE users.status = '1' 
ORDER BY seo_projects.last_updated DESC)

Open in new window


The problem I'm now having is that I can't seem to figure out how to do an "ORDER BY" clause on the whole result set.  It seems I'm only able to do them to the 2 separate queries individually, .. which isn't working right.  

Any ideas as to how I might accomplish this?

Thanks,
- Yvan
0
 
egoselfaxisAuthor Commented:
Nevermind .. I don't know what I was thinking.  I have it working now:

(SELECT  

users.companyname,
users.usertype,
users.whitelabel_company_id,
seo_clientrequests.ID AS theid,
seo_clientrequests.project_type AS the_project_type,
seo_clientrequests.due_date AS the_due_date,
seo_clientrequests.status AS status_percent_complete,
seo_clientrequests.subject AS subject_package_id,
seo_clientrequests.private AS private_waiting_on_reply,
seo_clientrequests.client_id,
seo_clientrequests.last_updated

FROM seo_clientrequests 

LEFT JOIN users ON seo_clientrequests.client_id = users.id 

WHERE users.status = '1') 

UNION ALL 

(SELECT 

users.companyname,
users.usertype,
users.whitelabel_company_id,
seo_projects.ID AS theid,
seo_projects.project_url AS the_project_type,
seo_projects.project_due_date AS the_due_date,
seo_projects.percent_complete AS status_percent_complete,
seo_projects.package_id AS subject_package_id,
seo_projects.waiting_on_reply AS private_waiting_on_reply,
seo_projects.client_id,
seo_projects.last_updated

FROM seo_projects 

LEFT JOIN users ON seo_projects.client_id = users.id 

WHERE users.status = '1')  

ORDER BY last_updated DESC 

Open in new window

0
 
egoselfaxisAuthor Commented:
Well, .. to be honest, .. not really.  

But your efforts were sincere and appreciated, .. so I will award them accordingly :)

Thanks - and have a great weekend!

- Yvan
0
 
Barry62Commented:
Well, I tried.  And, btw, I was the only one who did.  Thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.