1. Grant Singleton
  2. Valentina Database ADK
  3. Samstag, März 10 2018, 01:15 AM
  4.  Abonnieren via E-Mail
Greetings from Australia,

I have a main table Table1 which I have a binary link with Table2 and Table3. There is no data connection between Table2 & Table3...e.g:

A. Table1 -> [Binary Link] -> Table2
B. Table1 -> [Binary Link] -> Table3

I need an SQL SELECT command that can successfully extract all the data from Table1 with LEFT JOINS from Table2 & Table3 via the binary links. I'm adding the links manually via Studio Pro. I'm using an SQL command similar to this:

SELECT TABLE1.**, TABLE2.Field as T2F, TABLE3.Field as T3F FROM TABLE1 LEFT JOIN TABLE2 LEFT JOIN TABLE3 WHERE TABLE1.Field='SomeData' ORDER BY TABLE1.Field

The above generates a semantic error. However, if I remove the reference to Table3, it works. If I have other left joins that are connected via common fields, that also work. e.g.

LEFT JOIN Table7 ON Table1.CommonField=Table7.CommonField

Currently I can only get the data from the number A binary link as above. I want to be able to get the data from B binary link as above, with more similar links in the future to other tables.

Can someone please advise how to do it? I would prefer not to use ObjectPtr or Foreign Key. This is delaying my app development.

Thanks in advance.

Cheers
Grant
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
Hi Grant,

Please look at this - http://www.valentina-db.com/docs/dokuwiki/v8/doku.php?id=valentina:vcomponents:vkernel:vlink:binarylink_advantages&;s[]=join

Short form of join expression (without join condition) will work for cases when tables are linked in a single (straight) way.
You get an ambiguity error because t1 is linked to t2 and t3, so you should mention join condition. Binary link is a special kind of link which is not based on the table's data, so there is a special Valentina's syntax - link name instead of join condition (will work for any link kind).

Something like this one:


SELECT ...
FROM t1 LEFT JOIN t2 ON lnk_t1_t2
LEFT JOIN t3 ON lnk_t1_t3 ...
Kommentar
There are no comments made yet.
Grant Singleton Akzeptierte Antwort
Ivan,

Thank you so much for your response. You've solved my problem and now it works perfectly.

I owe you a drink!!

Cheers
Grant
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. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories