What could be the join query for the condition given below?

Question

I have two tables Orders table and customers table, both have customerid as common field,

customer table have firstname, lastname, phone and email address fields.

Now if I have to search/select the orders according to customer firstname and/or lastname and/or phone and/or email and/or orderid, then what should be the mysql format of join query?

Additional question related to above

I have to use a single text box to use for search by order id or first name or last name or phone or email address, how can i Identify the input value to be related to required fields, if u have the idea to make it happen plss guide me...

Answer 1

SELECT * FROM orders JOIN customers ON customers.customer_id=orders.customer_id;

Answer 2

Something like this:

select orders.* 
from orders 
join customers on customers.customerid=orders.customerid
where customers.firstname = 'John' -- or whatever

This gives you a list of orders, but you can filter it based on the fields in the Customers table (by changing the where clause appropriately).

To address the second question, you can't tell which field the user wants to search on, so you'll have to search on all possible fields. Build up a where-clause like this:

where (customers.firstname like '%input%')
   or (customers.lastname like '%input%')
   or (orders.id = 'input')
   or (.. etc)

Where input is what was typed in the textbox.

Be careful to sanitize the input string though. You should use parameters if you can, or at least replace ' with ''.

You don't want the user to be able to enter '; drop table customers; -- and have that interpreted as SQL code rather than a string.

Answer 3

SELECT * FROM customers AS c INNER JOIN order AS o ON c.customerid=o.customerid

To Order:

SELECT * FROM customers AS c INNER JOIN order AS o ON c.customerid=o.customerid ORDER BY c.lastname DESC

To Search:

SELECT * FROM customers AS c INNER JOIN order AS o ON c.customerid=o.customerid WHERE c.lastname LIKE "%brown%"