SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COUNT through 3 tables?

    Hi Chaps,

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

    Code:
    $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 ?!

  2. #2
    SitePoint Member
    Join Date
    Jan 2006
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try doing SELECT COUNT(*) FROM (SELECT .....) as `newtable` - that should work.

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Lemonzoo . . .

    Tried that but still getting 3 (instead of 6)....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •