Flexible search query using three tables

[mohammed24]

I have three tables Order,Customer,Address.

Can i know how to join three tables in flexible search to get below :

Customer name; E-mail address; Phone Number from address table; Country; Date of most recent order.

Any help would be appreciated?

[Former Member]

 SELECT * FROM
 ({{
      SELECT {c.name}, {c.uid}, {c.pk} FROM {Customer as c}
 }}) c
 JOIN
 ({{ 
     SELECT * FROM
    ({{
        SELECT {o.creationtime}, {o.user}, {o.deliveryaddress} FROM {Order as o}
    }}) o
    INNER JOIN
    ({{
        SELECT MAX({o.creationtime}) as mindate, {o.user} as minuser
          FROM {Order as o}
        GROUP BY {o.user}
    }}) x ON o.createdts=x.mindate
  }}) o
  ON o.p_user = c.pk
  JOIN
  ({{
      SELECT {a.cellphone}, {a.country}, {a.pk} FROM {Address as a}
  }}) a
  ON o.p_deliveryaddress = a.pk

[Former Member]

Hi,

Below query should return data you need:

 SELECT {c.name}, {c.uid}, {a.cellphone}, {a.country}, {o.creationtime} FROM {Customer as c}, {Address as a}, {Order as o} WHERE {c.pk} = {o.user} AND {o.deliveryaddress} = {a.pk} ORDER BY {o.creationtime} DESC LIMIT 1

Depending on your needs you might want to change o.creationtime to o.modifiedtime. You might also have to update fieldnames to match your custom modifications.

Best regards,

[mohammed24]

Thanks Jakub..When i use LIMIT it says incorrect syntax near LIMT?

[Former Member]

Please check if you didn't misspelled (LIMT). Alternatively you can remove LIMIT 1 and use TOP 1:

 SELECT TOP 1 {c.name}, {c.uid}, {a.cellphone}, {a.country}, {o.creationtime} 
 FROM {Customer as c}, {Address as a}, {Order as o} 
 WHERE {c.pk} = {o.user} AND {o.deliveryaddress} = {a.pk} 
 ORDER BY {o.creationtime} DESC

Regards,

[mohammed24]

both LIMIT & (LIMT) as well doesnt work for me...When i use TOP 1 its gives only one record out of 300 records in total.

[Former Member]

So do you want to get that information for most recent order globally (that's how I understood your question) or most recent order for every customer (or address)?

[mohammed24]

i want to fetch most recent order for every customer based upon the order creation time

[Former Member]

Please try this:

 SELECT {c.name}, {c.uid}, {a.cellphone}, {a.country}, MIN ({o.creationtime}) 
 FROM {Customer as c}, {Address as a}, {Order as o} 
 WHERE {c.pk} = {o.user} AND {o.deliveryaddress} = {a.pk} 
 GROUP BY {c.name}, {c.uid}, {a.cellphone}, {a.country}

Regards,

[mohammed24]

The above one still gives me two ormore than two order's.

[sachinsaxena_1]

Using "PagedFlexibleSearchService" is better way