Question about a SQL statement.

Classic Servoy
1

Im working on a picker routine where you can search en choose items for a branche or something else you would like. Ive got the SQL statement if you want to search in one table. But now I also want to search in two or more tables but now I get the error

Column owner_id found in more than one table – need a correlation name.

This is my generated SQL which I use:

var vQuery = "SELECT " + vTableColumns + " FROM " + vTables + " WHERE owner_id = " +ownerId + " AND owner_loc_id = " + ownerLocId + vCustomWhereClause

Now I fill those vars and it says that owner_id is in one or more tables. How can I solve this?

2

I am not really sure what is in all those variables, but you might need to qualify the columns as in

SELECT table.column FROM table WHERE table.column = ...
3

Well patrick,

This is the sql statement where he gives the error.

SELECT product_id, description, b.branche FROM product, branche AS b WHERE owner_id = 0 AND owner_loc_id = 0 AND (branche_id = b.branche)

the owner_id and owner_loc_id is located in all tables. And is static programmed in the SQL code.

4

Hi Rick,

You need to tell the database in what table you want to filter on owner_id since they exist in both tables. Something like this:

SELECT a.product_id, a.description, b.branche FROM product a , branche b WHERE a.owner_id = 0 AND a.owner_loc_id = 0 AND (a.branche_id = b.branche)

Hope this helps.

5

Hmm I also thought that. But the problem is that I automaticly generate this SQL with this code:

globals.core_DIALOG_showSelectDialog('SERVER, 'product, branche AS b','product_id, description, b.branche', COL_NAMES, 'product_type_id ASC', true,'branche_id = b.branche_id');

How do I format it in this way. Need to make the table part as a Array or something?

6

If every table you use has this owner_id then why not assign a default aliasname to the first used table. Then use this standard alias in your code for your owner_id. Of course you will need to use this alias for all the columns as well.

globals.core_DIALOG_showSelectDialog('SERVER, 'product myAlias , branche AS b','myAlias .product_id, myAlias.description, b.branche', COL_NAMES, 'myAlias.product_type_id ASC', true,'myAlias.branche_id = b.branche_id');
```Will result in:

SELECT myAlias .product_id, myAlias .description, b.branche FROM product myAlias, branche AS b WHERE myAlias.owner_id = 0 AND owner_loc_id = 0 AND (myAlias.branche_id = b.branche) ORDER BY yAlias.product_type_id ASC


Hope this helps
7

Thanks :)

I worked it out for now. But I used a parameter for it so you can define it in your own way