Flexible search query using three tables

Question

I have three tables Order,Customer,Address.

Edited :

I want to fetch all customer's who are registeretd with website. If the customer has placed any order's i want to fetch the latest order from list of order's, If customer has not placed any order then i want to put the field as blank as shown in below table

For eg :

       Customer_Name   Email ID   Country   Phone   Latest_Order_Code
       A               xxxx        xxxx     x       1234 

       B               yyyy        yyyy     y

       C               ffff        tttt     l       3456

       D               zzzz        iiii     o

Any help would be appreciated?

Answer 1

Refer to below query, where I have only fetch order code and Customer name. You can write more join and select fields based on your requirement.

select {o.code} as orderCode,
       {c.name} as name,
       {a.cellphone} as cellphone

from   {order as o 
        join Customer as c on {c.pk} = {o.user} 
        join Address as a on {o.deliveryaddress} = {a.pk}
       } 

       where {o.code} in ({{select max({code}) from {order} group by {user}}})

---

Update: Fetch all registered customers and their last order information

select t1.name, t2.orderCode, t2.cellphone

from 

({{
  select {pk} as userPk, {name} as name from {Customer}
}}) as t1

LEFT JOIN

({{
    select 
       {o.code} as orderCode,
       {o.user} as user,
       {a.cellphone} as cellphone

from   {order as o 
        join Address as a on {o.deliveryaddress} = {a.pk}
       } 

       where {o.code} in ({{select max({code}) from {order} group by {user}}})
}}) as t2

on t2.user = t1.userPk

---

Detail post how to write complex flexible search query join using the dynamic tables?