I have a query that searches through 3 tables and displays the results for Open Jobs for a specific customer:
Code: Select all
$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);I've tried a few ways but e.g., if there are 6 open jobs in total, for some reason by COUNT shows 3 ?!