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?
Sheldon LivingstonConsultantAsked:
Who is Participating?
 
Brian TaoSenior Business Solutions ConsultantCommented:
In this case you'll have to use "LEFT JOIN":
select orders.customerID, orders.orderID, customer.customerCompany 
from orders LEFT JOIN customer 
ON orders.customerID = customer.ID

Open in new window

0
 
Ray PaseurCommented:
Maybe something like this...

SELECT customerID, orderID, customerCompany
FROM orders, customer
WHERE orders.customerID = customer.ID
OR orders.customerID = NULL
0
 
Sheldon LivingstonConsultantAuthor Commented:
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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
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.
0
 
Sheldon LivingstonConsultantAuthor Commented:
Thank you Brian... this worked!
0
 
Dan CraciunIT ConsultantCommented:
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.