|
|
Home » Community » U++ community news and announcements » Sql: *JoinRef
Sql: *JoinRef [message #34744] |
Fri, 09 December 2011 14:14 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
It took some time and effort, but SqlExp is now able to create join conditions based on .sch file REFERENCES and PRIMARY_KEY attributes:
Select(ID(ID, NAME, LASTNAME))
.From(TABLE1).LeftJoinRef(TABLE2)
.Where(BDATE == Date(2011, 12, 9))
produces
select ID.ID, ID.NAME, ID.LASTNAME
from TABLE1 left outer join TABLE2 on TABLE2.TABLE1_ID = TABLE1.NAME
where BDATE = date '2011-12-09'
it is also possible to use On clause anyway - it gets combined with 'and':
Select(ID(ID, NAME, LASTNAME))
.From(TABLE1).LeftJoinRef(TABLE2).On(IsNull(BDATE))
.Where(BDATE == Date(2011, 12, 9))
select ID.ID, ID.NAME, ID.LASTNAME
from TABLE1 left outer join TABLE2 on TABLE2.TABLE1_ID = TABLE1.NAME and BDATE is NULL
where BDATE = date '2011-12-09'
|
|
|
|
|
|
|
Re: Sql: *JoinRef [message #34976 is a reply to message #34975] |
Fri, 23 December 2011 08:40 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
To replace "in" (nested select), you have to use normal InnerJoin, not LeftJoin.
I suppose that this should work:
Select(myData.Of(TABLE_A)).From(TABLE_A)
.InnerJoinRef(TABLE_B).On(AGE.Of(TABLE_B) == xxxxxx).Where(A_REF_C.Of(TABLE_A) == yyyyyy);
Here I expect yyyyyy to be single value, not to represent another "in"...
[Updated on: Fri, 23 December 2011 08:48] Report message to a moderator
|
|
|
|
Re: Sql: *JoinRef [message #34978 is a reply to message #34977] |
Fri, 23 December 2011 17:24 |
Didier
Messages: 680 Registered: November 2008 Location: France
|
Contributor |
|
|
I tried it and it works in one case and not in another case
The only difference between the two cases which have exactly the same select line, is that:
- case 1: TABLE_A has 1 ref ==> OK
- case 2: TABLE_A has 2 refs ==> Does not work
mirek wrote on Fri, 23 December 2011 08:40 |
Here I expect yyyyyy to be single value, not to represent another "in"...
|
==> Yes
[Updated on: Fri, 23 December 2011 17:26] Report message to a moderator
|
|
|
|
Re: Sql: *JoinRef [message #35411 is a reply to message #34979] |
Fri, 10 February 2012 19:12 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
After some experiences I have to say that this feature is a little bit dangerous, as it can eventually pick wrong 'connection' between tables. Worse, it can in some cases even break existing code by adding columns to tables. (Google "natural join sql" which is quite similar feature for explanation).
However, it still seems very useful and works just fine in most cases. To fix "wrong connection" problem, I have introduced heuristics that it first tries to pick foreign key with the same name as primary or foreign key key that has name table_pk (if FOO has primary key ID, then foreign key has to be FOO_ID) - this covers two most common naming conventions. This has fixed "picked wrong connection" problem for me.
As for other problem (instability in SQL issues), I guess that given this heuristics, it is something that can be avoided when designing and upgrading DB schema. I mean, if DB schema makes any sense, things should work.
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 03:50:23 CEST 2024
Total time taken to generate the page: 0.05947 seconds
|
|
|