TOPIC: Why this join query doesn't work ?
#1733
Why this join query doesn't work ? 8 Months, 2 Weeks ago
I can't understand why the join query bellow doesn't work:

error: join by doesn't match columns

Code:


(dadmaster rename dad_id to dad_master_id) 
join by(dad_master_id) daddetail



Code:


function()
{
try Database('drop daddetail')
try Database('drop dadmaster')

Database('
ensure dadmaster(dad_id, dad_name)
key(dad_id)
index(dad_name)
')
Database('
ensure daddetail(dad_id, dad_master_id, dad_name)
key(dad_id)
index(dad_master_id) in dadmaster(dad_id)
index(dad_name)
')

Transaction(update:)
{ |t|
t.Query("insert {dad_id: 1, dad_name: 'one'} into dadmaster")
t.Query("insert {dad_id: 2, dad_name: 'two'} into dadmaster")
t.Query("insert {dad_id: 3, dad_name: 'tree'} into dadmaster")
for(i=1; i < 20; i++)
{
mid = Random(3)
if mid is 0
mid = 1
Print(mid)
t.QueryOutput('daddetail', Record(dad_id: i, dad_master_id: mid, dad_name: String(i)))
}
}

//Database('drop daddetail')
//Database('drop dadmaster')
}

 
 
#1735
Re:Why this join query doesn't work ? 8 Months, 2 Weeks ago
Replying to myself some time later:

Comming from a SQL background it's a bit hard to remember that suneido's query similarity syntax to SQL syntax is only at keywords in most cases, the internal functioning is not the same.

In the previous query I was taking for sure that specifying "by(columns)" to join will limit the join to only the specified fields but it doesn't and the user's manual say that but is easy to forget it.

Working query:
Code:


(dadmaster rename dad_id to dad_master_id rename dad_name to dad_master_name) 
join by(dad_master_id)  daddetail



Without project to filter duplicate column names the join operator try match then (duplicated column names) even specifying a "by(column)" clause.
 
 
#1738
Re:Why this join query doesn't work ? 8 Months, 2 Weeks ago
Yes, it is different, sorry. One reason is so that the result can be a single record, with unique field names. This also makes it easier to compose complex queries.

Originally we did not have by(...) and it is still optional.

It is useful to ensure that you are joining on the correct fields.

For example, if you add fields to existing tables, you can "break" joins. If you use by(...) you will get an error so you know.
 
 
andrew