Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

3 way join SQL

I'm trying to connect 3 tables in my database: organizations, category_values and users_categories. If I change this:
where (uc.uid = '3' AND cv.catId='163')
to this:
where (uc.uid = '3')
I get all of the results I would expect. But adding the cv.catId I get 0 results (query runs with no errors). Is there something structurally wrong with my query?
$query = "select distinct o.orgId, o.name, cv.catId
	from (organizations o join category_values cv
	on cv.orgId = o.orgId) join users_categories uc
	on uc.catId = cv.catId
	where (uc.uid = '3' AND cv.catId='163')
	order by o.name ASC";

Open in new window

0
level9wizard
Asked:
level9wizard
  • 3
  • 2
1 Solution
 
level9wizardAuthor Commented:
As some more info, when I print row data the catId is the same for every row result. As if it's taking it from uc.catId and not cv.catId
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
$query = "select distinct o.orgId, o.name, cv.catId
        from organizations o join category_values cv
        on cv.orgId = o.orgId 
        left join users_categories uc
        on uc.catId = cv.catId and uc.uid = '3' AND cv.catId='163'
        order by o.name ASC";

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
otherwise, you might not have any rows matching both criteria?!
0
 
level9wizardAuthor Commented:
I got the answer I was looking for thru IRC, thought i'd share...

$query = "SELECT o.orgId, o.name, cv.catId FROM organizations o join (
SELECT DISTINCT cv2.orgId FROM users_categories uc JOIN category_values cv2 on uc.catId=cv2.catId WHERE uc.catId=200 and uc.uid=3 ) as d ON d.orgId=o.orgId
JOIN category_values cv ON d.orgId=cv.orgId
WHERE cv.catId='163'";
0
 
level9wizardAuthor Commented:
The real solution to my problem is below, but I want to award you points for trying. Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now