1. Kevin
  2. Valentina Studio
  3. Monday, November 23 2020, 04:39 PM
  4.  Subscribe via email
SQLite database.

I have two tables. Their Link has been pre-established: one to many.

I add the "one" table in the Query Editor.

I select some fields from the "one" table.

I add the "many" table to the right of the "one" table in the Query Editor.

The pre-established link automatically appears. It appears as an INNER JOIN.

I change the join to LEFT OUTER JOIN.

The join icon correctly displays as a LEFT OUTER JOIN as shown here:

roducts:vstudio:help:query_editor" target="_blank" rel="nofollow">https://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:products:vstudio:help:query_editor

I select some fields from the "many" table.

The query does not produce the result I was expecting: I know some of the "one" table entries do not have entries in the "many" table, however they do not appear in the result.

The Query tab: the "many" table is the FROM table.

The Query tab: the FROM table change (from the "one" table to the "many" table) occurs immediately after I add the "many" table.

If I remove the link, add the link, change the join to LEFT OUTER JOIN, the query produces the result I was expecting. However, the join icon displays a RIGHT OUTER JOIN.

(Per my notes, I have experienced this in the past.)

Windows 10 2004 64-bit
Valentina Studio Pro 10.5.6 (64-bit)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
Hello Kevin,

Yes, I reproduced it, thank you.
The order of tables in JOIN is wrong - "many" LEFT JOIN "one" instead of "one" LEFT JOIN "many".
They must be joined in the order of adding to the diagram.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Kevin Accepted Answer
Sergey,

Thanks. So, should I add the "many" first? Then the "one"? Does it matter where they are on the diagram? It appears with this method I have to remove the link and add the link to get the correct query.

Kevin
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
Now the order of adding tables is not used
"Many" table is always at the left side of the join - we have to fix it.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Kevin Accepted Answer
I come from Access. I see how they solve the order of tables (as added to the diagram) and locations of tables ... when one right clicks on the link / join and goes to the option to define / change the join, one is presented with several options ... details regarding the relationship of the tables. For example: one to one, one (all) (name of table) to many (equal) (name of table), and one (all) (name of table) to many (equal) (name of table). If one clicks on one of the latter two it appears the location of tables is calculated / determined and the SQL query is given a LEFT or RIGHT join, accordingly. For SQLite, one could be presented with both one to many as LEFT and the choice would correctly complete the SQLite query.

Thank you.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Sergey Pashkov Accepted Answer
Hello Kevin,

In the next version, the tables will be joined in the same order as they were added to the diagram.
Yes, I think we'll add a dialog to change the join (to swap left/right tables and change key fields)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories