Question Re: Flexible search query using three tables in CRM and CX Q&A

Flexible search query using three tables

mohammed24 — Fri, 30 Mar 2018 08:34:31 GMT

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?

Re: Flexible search query using three tables

Former Member — Fri, 30 Mar 2018 12:39:34 GMT

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,

Re: Flexible search query using three tables

mohammed24 — Fri, 30 Mar 2018 13:09:35 GMT

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

Re: Flexible search query using three tables

Former Member — Fri, 30 Mar 2018 13:40:42 GMT

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,

Re: Flexible search query using three tables

mohammed24 — Fri, 30 Mar 2018 13:48:51 GMT

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.

Re: Flexible search query using three tables

Former Member — Fri, 30 Mar 2018 14:01:52 GMT

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)?

Re: Flexible search query using three tables

mohammed24 — Fri, 30 Mar 2018 14:05:43 GMT

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

Re: Flexible search query using three tables

Former Member — Fri, 30 Mar 2018 14:34:39 GMT

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,

Re: Flexible search query using three tables

mohammed24 — Mon, 02 Apr 2018 07:20:04 GMT

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

Re: Flexible search query using three tables

sachinsaxena_1 — Mon, 02 Apr 2018 15:22:28 GMT

Using "PagedFlexibleSearchService" is better way

Re: Flexible search query using three tables

Former Member — Thu, 05 Apr 2018 12:26:17 GMT

 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