Filtering by number of related records

Classic Servoy
1

I have two tables (stock and posting) where each row in stock can have one or more in posting.

I want to get a foundset (for use in a form based on stock) containing only the rows in stock that have <=1 related record in stock. It’s the SQL query required I’m stuck with, any help much appreciated.

The foreign key in my posting table is called stock_id, and the primary key of stock is also stock_id.

So far I’ve got…

var q = "SELECT stock_id from stock where ???? "; //
	
	databaseManager.getDataSetByQuery( controller.getServerName(), q, null, -1) 
	controller.find();	
	stock_check_date >= "01/09/2009";
	controller.search(true);
	controller.sort("location");
2

I guess you need something like this:

SELECT stock.stock_id FROM stock WHERE stock.stock_id NOT IN (SELECT stock.stock_id 
FROM stock 
JOIN posting ON stock.stock_id=posting.stock_id 
GROUP BY stock.stock_id 
HAVING COUNT(posting.stock_id) > 1)

Hope this helps.

3

Or perhaps more efficient:

SELECT stock.stock_id
FROM stock 
LEFT JOIN posting ON stock.stock_id=posting.stock_id 
GROUP BY stock.stock_id 
HAVING COUNT(posting.stock_id) <= 1

But this might error because of NULL values.

4

That seems to work fine, thank you so much :-) I can’t wait till I’m that fluent in SQL.

5

Which if the 2 queries worked for you ? :)

6

Thanks for your interest, I feel very supported here… even though I am strongly considering retreating to the comfort of FileMaker Pro 11.

I have only tried the first query so far, it works but is really quite slow even with only a few 1000 records. I have a LOT of NULLs in my database at the moment, something I hope to resolve with transactions later (if I stick with Servoy)

Give me a few days to decide if I persist with all the learning required for Servoy, or retreat to FileMaker Pro. I’ll write a separate post about this.

7

Servoy 5 has built-in search on related aggregates:

Create a relation stock_to_posting and an aggregate n = count(posting_id) on the posting table.

if (foundset.find())
{
     stock_to_posting.n = '<=1'
     founset.search()
}

Rob

8

Actually, previous example only returned the stock records with 1 related record, the ones without related records were missing.
This is fixed in next 5.2 release.

Rob

9

Thanks for letting me know before I discovered it myself, much appreciated.

Any rough idea when 5.2 is to be released?

10

This change is included in 5.2 rc1 which is released last week.
Servoy 5.2 final is expected to be released this month.

Rob