1. Kevin
  2. Valentina Studio
  3. Montag, November 23 2020, 04:39 PM
  4.  Abonnieren via E-Mail
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)
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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)
Kommentar
There are no comments made yet.
Kevin Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Kevin Akzeptierte Antwort
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
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
  • Seite :
  • 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