Need help making query efficient

[ywickham]

I was wondering if someone could help me make the following query efficient. It's tying up a lot of resources:

$query = "SELECT * FROM user, usergroup, userfield
WHERE user.usergroupid = usergroup.usergroupid
AND user.userid = userfield.userid
AND user.usergroupid = $report_id
OR user.usergroupid = $report_id;";

Basically, the problem seems to come from when I try to join two usergroupid's with multiple report_ids.

Any help would be appreciated.

Thanks.

[barand]

Use inner joins and remove duplicated OR condition

$query = "SELECT * FROM (user INNER JOIN usergroup
ON user.usergroupid = usergroup.usergroupid )
INNER JOIN userfield ON user.userid = userfield.userid
WHERE user.usergroupid = $report_id ";

hth

[ywickham]

Thank you Barand.

But what if the duplicate join needs to be there. For instance, what if I was querying for not 1 but 2 or more $report_id values? Wouldn't I need to have the duplicate OR condition...or is there another way to address that?

Thanks again!

[tomhath]

Use IN rather than =

$query = "SELECT * FROM (user INNER JOIN usergroup 
ON user.usergroupid = usergroup.usergroupid ) 
INNER JOIN userfield ON user.userid = userfield.userid 
WHERE user.usergroupid in ($report_id1, $report_id2) "; 

[ywickham]

Ah...thank you. I will try that and report back.

[ywickham]

Thank you guys. The combination of the two was exactly what the doctor orders.

I appreciate your help.