CODEWITHSUNDEEP
phpmysqlsql
user1629095
user1629095

Reputation: 33

Combining two tables different criteria

basically im just trying to finish off a project im working on, having a little trouble finding the right syntax to use for this SQL statement.

Basically what i have two different tables:

Customer:
companyid
companyname
etc etc.

Machine:
machineid
model
serial
companyid

Now usually this would be easy as i would just join the companyid, however, i need to do this slightly differently this time. I need to return specific data from the tables of customer using the customers id to search, and specific data from the tables of machine, using the machine id to search.

Im pretty tired so i do apologise if the answer is staring me straight in the face, but heres what i was working on, again i know its more than likely wrong so i am sorry i have tried searching but to no avail:

$customerquery = mysql_query("
            SELECT customer.companyid, customer.companyname, 
                   customer.companyaddress, customer.postcode, 
                   customer.telephone, customer.mobile, 
                   machine.machineid, machine.model, 
                   machine.serial 
            FROM customer, machine 
            WHERE customer.companyid=$customerid AND 
                  machine.machineid=$machineid
            ");

Any help would be greatly appreciated, Thankyou!

Upvotes: 0

Views: 77

Answers (1)

John Woo
John Woo

Reputation: 263823

Your current query produces cartesian product since you miss the condition on where the tables should be joined. This is an old syntax of join (SQL-89)

SELECT customer.companyid, customer.companyname, 
       customer.companyaddress, customer.postcode, 
       customer.telephone, customer.mobile, 
       machine.machineid, machine.model, 
       machine.serial 
FROM   customer, machine 
WHERE  customer.companyid = $customerid AND 
       machine.machineid = $machineid AND
       customer.companyid = machine.companyid -- you missed this one producing
                                              -- cartesian product

New syntax of join (SQL-92)

SELECT customer.companyid, customer.companyname, 
       customer.companyaddress, customer.postcode, 
       customer.telephone, customer.mobile, 
       machine.machineid, machine.model, 
       machine.serial 
FROM   customer INNER JOIN machine 
          ON customer.companyid = machine.companyid 
WHERE  customer.companyid = $customerid AND 
       machine.machineid = $machineid

Upvotes: 1

Related Questions