asked on

PHP MySQL query to return potential null fields

I have 2 "order" records in a table. My query reads as follows:

select customerID, orderID, customerCompany from orders, customer where orders.customerID = customer.ID

This will display the customer ID, order ID and the friendly customer name.

One order has a customerID while the other doesn't. Only the record with the customerID shows. Can I remedy this via a query modification?

Ray Paseur

Maybe something like this...

SELECT customerID, orderID, customerCompany
FROM orders, customer
WHERE orders.customerID = customer.ID
OR orders.customerID = NULL

Sheldon Livingston

ASKER

Sorry Ray... I probably should have been clearer maybe? Your query still only returns the one order. I'll show table.field:

select orders.customerID, orders.orderID, customer.customerCompany

from orders, customer

where orders.customerID = customer.ID

ASKER CERTIFIED SOLUTION

Brian Tao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

Try omitting the WHERE clause and instead GROUP BY customerID -- what does that get you? Sorry, but without a test data set, we're kind of guessing.

Sheldon Livingston

ASKER

Thank you Brian... this worked!

Dan Craciun

Can you post sample data and the result you are expecting?

You can replace it with a left join, which should give you all orders:

SELECT customerID, orderID, customerCompany
FROM orders LEFT JOIN customer
ON orders.customerID = customer.ID

Much later edit: looks like I type too slow :)