SQL help needed

Classic Servoy
1

Hi there,

I need some SQL help here…

I have two tables:
table 1: users, with column: usersid and name
(In this table there are 5 records)

usersid   name
1            Jan
2            Piet
3            Johan
4            Klaas
5            De paashaas

table 2: calendaruser, with column calendaruserid, usersid, and usershareid

there is a simple relation between this two tables: usersid = usersid

for example if I am in the first record(user: Jan) of the table users,
I see than two relational records in the calendaruser:

calendaruserid  usersid   usershareid
1                     1            2
2                     1            3

What I want to retrieve in ONE sql-query is a dataset of:

1.) the usersid and the name of the currentuser (that’s not so hard) AND
2.) the usersid and the name of user 2 and 3 (they are in these two relational record)

so I want a dataset that looks in this case like:

usersid   name
1            Jan
2            Piet
3            Johan

Please, teach me how to do this! :)

2

You’ll have to double check the syntax for correctness, but the general idea is here…

Assuming you fill the ? with the userID of the person logged in…

SELECT users.usersid, users.name FROM users
	WHERE users.usersid = ?
	OR users.usersid IN (
		SELECT calendaruser.usershareid FROM calendaruser
		WHERE calendaruser.usersid = ?)

The first part..
WHERE users.usersid = ?
Finds the currentuser.

The rest below uses the IN statement to find the others.

3

Regarding creating unusual or creative SQL syntax… there is a book that I’ve used with great satisfaction. It is the SQL Cookbook from O’Reilly, and has a 5 star rating on Amazon. It solves cool and interesting SQL problems with great descriptions and syntax for a number of different db products.

http://www.amazon.com/gp/product/059600 … oding=UTF8

Best,

Rich Coulombre

4

Before I order the book :wink:, I try this query, but without results…

SELECT * FROM invoices WHERE companyid LIKE companyid AND warning_text != “”

Any suggestions?

5 
var query = "SELECT * FROM invoices WHERE companyid LIKE '" + companyid + "' AND warning_text != ''"

or

var query = "SELECT * FROM invoices WHERE companyid LIKE ? AND warning_text != ?

var args[0] = companyid 
args[1] = warning_text
6

Thanks Harjo! Works excellent now…