Multi Query Issue

[arggg]

I have 3 tables I need to get data from. Which as fields I need from each.

opencases_daily - Assigned_To, Prov_Group
tsms - Group, Name
prov_groups - gid, group_name

The opencases_daily has multiple entries with the tsms.Name in it. However I will specify the Prov_Group and need to get the count each time the Prov_Group= what i specify for the Assigned_To. So Jack could have multiple entries in opencases daily but I need the number of times he appears in there with the Prov_Group = 1234 but only if he is in tsms. This works good however there are some rows in opencases_daily that have the Assigned_To blank or they do not exist in tsms in which case I want to make their name 'Other' so I can display it as a name with Jack.

Here is the code I have so far but can anyone think of how I can have it change anyone with Prov_Group = 1234 and their name is not in tsms then it will increment 'Other'?

$sql = "SELECT * FROM tsms ORDER BY `Name`";
 
        $sql = $db->query($sql);
        if(is_numeric($_REQUEST[2]))
        {
            $group = $func->groupNum2Name($_REQUEST[2]);
        }
        while ($res = $db->fetchArray($sql,MYSQL_ASSOC))
        {
 
            $sql3 = "SELECT COUNT(*) FROM opencases_daily WHERE ".
                "DATEDIFF(CURDATE(),STR_TO_DATE(Last_Out_Note, '%m/%d/%y')) ".
                ">= 6 AND `Assigned_To`='".$res['Name']."'".
                $func->iif(is_numeric($_REQUEST[2]),' AND `Prov_Group`=\''.$group,'')."'";
                $sql3 = $db->query($sql3);
 
            $num = $db->result($sql3);
            # Check if there are events for the user.
            if ($num > 0)
            {
                // Assign data for pie chart
                $data[] = $num;
                $targets[] = "javascript:LoadData('/tsm/".$res['uid']."')";
                $lbl[] = $res['Name'];
                $alts[] = "Events %d";
            }
        }

[arggg]

anyone know of a way to do this?

[pbmods]

Heya, Arggg.

I'm not sure I understand what you're trying to accomplish. Can you give an example of what 'correct' output might look like?

[coolsti]

It is quite possible that you can perform what you wish in one (or maybe a few) simple queries.

For people to help you with that, however, you should post the structure of the tables, and then a short description of the update that you are attempting. If the tables are very large (many columns) you can always just post here the ones that are essential for us to understand how to help you.

Try posting the table structure (or abbreviated table structure) with the "create table" syntax, for example as a copy-paste of the "show create table" command. Then someone like myself can copy/paste into a test database and try some things.