Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    Sep 2006
    Posts
    7
    Member #
    13991
    Hello all, I just have a small problem with one of my SQL statements.

    I am trying to take values from 4 tables;

    "JobTable" which contains a list of unique values and serves as the master list
    "Invoice" contains values and a field named "JOB_NUMBER" which references the "JOB_ID" in the JobTable
    "Estimate" is the same as "Invoice" table
    "WorkOrder" is the same as "Invoice" table

    I try to join the tables and count the number of Invoice, Estimate and WorkOrder occurrances of "JOB_ID" that are in these tables' "JOB_NUMBER" field.

    Here's my query:
    Code:
    $sql=mysql_query("SELECT JobTable.JOB_ID, Invoice.JOB_NUMBER, Estimate.JOB_NUMBER, WorkOrder.JOB_NUMBER,
     COUNT(Invoice.JOB_NUMBER), COUNT(Estimate.JOB_NUMBER), COUNT(WorkOrder.JOB_NUMBER), 
    FROM JobTable, Invoice, Estimate, WorkOrder WHERE JobTable.JOB_ID=Invoice.JOB_NUMBER 
    AND JobTable.JOB_ID=Estimate.JOB_NUMBER AND JobTable.JOB_ID=WorkOrder.JOB_NUMBER 
    AND Invoice.IS_DELETED='0' AND Estimate.IS_DELETED='0' AND WorkOrder.IS_DELETED='0' 
    GROUP BY Invoice.JOB_NUMBER");
    and here's the error I am getting:
    Code:
    Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ... on line ...
    I am sure it's just a small error on my part, but I am relatively new to doing joins and such on my data.

    Brad

  2.  

  3. #2
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Run the query directly in something like phpMyAdmin to see the error MySQL is giving.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  4. #3
    Junior Member
    Join Date
    Sep 2006
    Posts
    7
    Member #
    13991
    Here's my error via phpMyAdmin:

    Code:
    MySQL said: 
      #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM JobTable , Invoice , Estimate , WorkOrder WHERE JobTable . JOB_ID = Invoice' at line 1

  5. #4
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    You'll have to actually JOIN the tables using a JOIN clause. Something like:
    Code:
    SELECT JobTable.JOB_ID, Invoice.JOB_NUMBER, Estimate.JOB_NUMBER, WorkOrder.JOB_NUMBER, COUNT(Invoice.JOB_NUMBER), COUNT(Estimate.JOB_NUMBER), COUNT(WorkOrder.JOB_NUMBER) FROM JobTable JOIN Estimate ON JobTable.JOB_ID = Estimate.JOB_NUMBER JOIN WorkOrder ON JobTable.JOB_ID = WorkOrder.JOB_NUMBER JOIN Invoice ON JobTable.JOB_ID = Invoice.JOB_NUMBER WHERE Invoice.IS_DELETED='0' AND Estimate.IS_DELETED='0' AND WorkOrder.IS_DELETED='0'GROUP BY Invoice.JOB_NUMBER
    I think that's right, anyway :-P


Remove Ads

Posting Permissions

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

Search tags for this page

how 4table join with sql and php

Click on a term to search for related topics.
All times are GMT -6. The time now is 03:57 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com