I'm trying to figure way to join SharePoint lists using a a SQL like SELECT statement.
I have the following tables:
[customer details]
[customer notes]
[customer reviews]
all these have a common customer ID so I'm trying to do a SELECT across all three so that I can build a SharePoint list that will output the three tables when a customer number is entered in the search bar.
e.g.
select
Customer 101
RETURN
101[customer details]
101[customer notes]
101[customer reviews]
Hope you can help
You should take a look at using Lookup Columns. These allow you to create relationships between lists the easiest approach.
In addition to the previous answer have a look at joins and projections using caml.
There is no "SQL-Like syntax" for SharePoint.
Additionally, as a "rule of thumb": If you can build a relational model of your data, most probably SharePoint is not the best way to store the data.
This looks like something that can be achieved using SharePoint search and content types. https://medium.com/niftit-sharepoint-blog/display-search-results-in-sharepoint-online-b4784d48a9d5
I Ended up using lookup columns which seem to have worked very well for me. I put all the data until 1 table rather than taking a normalisation approach from the database theory.
