asked on

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

level9wizard

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

otherwise, you might not have any rows matching both criteria?!

level9wizard

ASKER

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'";

level9wizard

ASKER

The real solution to my problem is below, but I want to award you points for trying. Thanks!