[SOLVED] COUNT through 3 tables?

[kool_samule]

Hi Chaps,

 

I have a query that searches through 3 tables and displays the results for Open Jobs for a specific customer:

 

$colname_rsOpen_Cust = "-1";
if (isset($_GET['id'])) {
  $colname_rsOpen_Cust = $_GET['id'];
}
mysql_select_db($database_conndb2, $conndb2);
$query_rsOpen_Cust = sprintf("SELECT*
FROM((
SELECT 
tbl_projects.projid, 
tbl_projects.projtype,
tbl_jobs.jobname, 
tbl_jobs.jobid,
tbl_jobs.jobwnet, 
tbl_jobs.jobprepared, 
tbl_jobs.jobtranscomplete, 
tbl_jobs.jobftype, 
tbl_jobs.jobcorrec, 
tbl_jobs.jobcordoc, 
tbl_jobs.jobshipped, 
tbl_customers.custid,
tbl_customers.custname, 
tbl_languaget.langtname 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobs 
ON tbl_projects.projid=tbl_jobs.FK_projid 
INNER JOIN tbl_customers 
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobs.FK_langid 
) 
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtype,
tbl_jobtransline.jobname, 
tbl_jobtransline.jobid,
tbl_jobtransline.jobwnet, 
tbl_jobtransline.jobaccepted AS jobprepared, 
tbl_jobtransline.jobtranscomplete, 
tbl_jobtransline.jobftype, 
tbl_jobtransline.jobcorrec, 
tbl_jobtransline.jobcordoc, 
tbl_jobtransline.jobshipped, 
tbl_customers.custid,
tbl_customers.custname, 
tbl_languaget.langtname 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobtransline 
ON tbl_projects.projid=tbl_jobtransline.FK_projid 
INNER JOIN tbl_customers 
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobtransline.FK_langid 
)
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtype, 
tbl_jobxml.jobno AS jobname, 
tbl_jobxml.jobid, 
tbl_jobxml.jobwnet, 
tbl_jobxml.jobdownload AS jobprepared, 
tbl_jobxml.jobtranscomplete, 
tbl_jobxml.jobftype, 
tbl_jobxml.jobcorrec, 
tbl_jobxml.jobcordoc, 
tbl_jobxml.jobshipped,
tbl_customers.custid,  
tbl_customers.custname, 
tbl_languaget.langtname 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobxml
ON tbl_projects.projid=tbl_jobxml.FK_projid 
INNER JOIN tbl_customers 
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobxml.FK_langid 
)) SomeAlias
WHERE 
custid = %s 
AND jobshipped='n'", 

GetSQLValueString($colname_rsOpen_Cust, "int"));

$rsOpen_Cust = mysql_query($query_rsOpen_Cust, $conndb2) or die(mysql_error());
//$row_rsOpen_Cust = mysql_fetch_assoc($rsOpen_Cust);
$totalRows_rsOpen_Cust = mysql_num_rows($rsOpen_Cust);

 

What I need is a COUNT of all the "jobid".

 

I've tried a few ways but e.g., if there are 6 open jobs in total, for some reason by COUNT shows 3 ?!