SQL query question

Classic Servoy
1

Can relationships be used in SQL queries? Is the following a valid structure?```
var query = "SELECT crid FROM cr WHERE comid != null and comid$cr_to_com.company_name = null ";


At the moment I'm getting zero results in a dataset which should return at least a dozen records.
2

Hi Morley,

If you want to do an SQL query then I think you need to describe relationships in SQL language rather than using ‘Servoy’ relationships. I’m not sure from your question to which table the field you describe belongs to nor what the relationship is between the two tables so let’s just take a simple example to describe.

You have table A and table B linked by A.a_id = B.a_id (in other words A is the parent table and B is the child table, a_id is the primary key on A and a_id is the foreign key on B). If I want to find all records in Table A that have a value in ‘fieldA’ but do not have any child records in Table B then I would do the following to show it in Servoy:

SELECT a_id
FROM A
WHERE fieldA is not null and fieldA <> ''
AND a_id NOT IN
    (SELECT a_id
    FROM B)

‘NOT IN’ queries are not as efficient as ‘IN’ or ‘=’ queries but if the tables are not too large then it won’t make any difference.
HTH

3

Morley,

Servoy relations are Servoy relations. How could your database know about them?

I recommend reading an introduction to SQL. In the forum there are several links mentioned to decent learning sites. SQL is fairly easy.

Your case could be something like this:

SELECT crid FROM cr WHERE comid IS NOT NULL AND NOT EXISTS (SELECT primary_key FROM foreign_table WHERE cr.foreign_key = foreign_table.primary_key)

I don’t know how your relationship looks like. The uppder statement means that your relation cr_to_com links the table cr to the table “foreign_table” where cr.foreign_key = foreign_table.primary_key. You just have to replace those terms by the actual table and column names.

In your statement two things go wrong:

  1. You cannot use a Servoy relation (as stated above)
  2. SQL is not Java Script, so something like “!=” or “= null” won’t work in SQL

Why don’t you try to retrieve your records inside your database using an SQL tool first? If you have figured out the right statement, you just need to paste it into your method and replace some placeholders by variables/field values.

Hope this helps
Patrick

4

Morley,

This is a great resource for prgraming langauges,

http://www.w3schools.com/sql/default.asp

Erich

5

Thanks Patrick. Your suggestion works. I incorporated your earlier recommendation of table alias to keep everything straight.

var maxReturnedRows = 100000;
var query = "SELECT t1.callsid FROM calls t1 WHERE t1.peoid IS NOT NULL AND NOT EXISTS ";
query += "(SELECT t2.people_id FROM people t2 WHERE t1.peoid = ";
query += "t2.people_id)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[],maxReturnedRows);
controller.loadRecords(dataset);