A join query for three tables!! for the given condtion below

Question

I have three tables Orders table and customers table and orderstatus table, both order and customer table have customerid as common field,and order and orderstatus have order_status_id 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 a mysql format of join query so that I can combine all the three tables to get the results from orders table along with orderstatus from orderstatus table?

Answer 1

Well, I would start with LEFT JOINING all three tables together to start.

Then; structure your where clause to have a line like this for each parameter (basically; only applying filters in the case that parameters are provided)

WHERE 
(@customerFirstName IS NULL OR @customerFirstName = customers.FirstName)
AND (@customerLastName IS NULL OR @customerLastName = customers.LastName)
etc...

Depending on the number of records you're expecting; or if performance is lacking; you might want to think about creating a table variable to hold a smaller subset of data. (i.e. create a #customers table; and then only insert into that customers that match customer search criteria. Then join that to your other two tables)

Let me know if i've misunderstood your question; or if you'd like any clarifications.

Answer 2

Something like:

SELECT `orders`.* , 
 `orderstatus`.`description`,
 `customers`.`firstname`, `customers`.`lastname`, `customers`.`phone`,`customers`.`email`
FROM `orders`
LEFT JOIN `customers` ON `customers`.`id`=`orders`.`customerid`
LEFT JOIN `orderstatus` ON `orderstatus`.`orderid`=`orders`.`id`
WHERE `customers`.`lastname`='blogs'

I'm not sure of your orderstatus table.

Answer 3

You can just join the tables. No need for outer joins or anything according to your current data definition.

SELECT * 
FROM Orders o, customers c, orderstatus s
WHERE o.customerid = c.customerid
AND o.order_status_id = s.order_status_id
AND c.firstname = 'OM'
AND c.LASTNAME = 'The Eternity'
AND o.orderid = 752
AND ...