If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
joining on dates
is it possible to join on dates?
for example inner join mytbl on date() = mytbl.eff_dt and date() = nz(mytbl.exp_dt,date()) |
#2
|
|||
|
|||
joining on dates
Are you trying to join two copies of your table? The two you provided in
your example are both "mytbl" ... and if you only have one table, you don't need to 'join'. Regards Jeff Boyce Microsoft Office/Access MVP "joemeshuggah" wrote in message ... is it possible to join on dates? for example inner join mytbl on date() = mytbl.eff_dt and date() = nz(mytbl.exp_dt,date()) |
#3
|
|||
|
|||
joining on dates
joemeshuggah wrote:
is it possible to join on dates? for example inner join mytbl on date() = mytbl.eff_dt and date() = nz(mytbl.exp_dt,date()) Joining on dates is certainly possible, but 1. As jeff says, a join does not seem relevant here, unless you are deliberately doing a self-join? 2. If you are joining on date/time fields that store data other than midnight, results might not be what you expect. In any case, have you tried your idea? Did it fail? What symptoms led you to believe it failed? I.E., what prompted you to ask this question? :-) -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
joining on dates
i wanted to add the date expression to the join as opposed to the where...
SELECT M.MODULE_NM, R.SPRT_REP_NM, A.ACCT_GRP_NM, RP.PROFILE_ID FROM (((((REP R INNER JOIN REP_PROFILE RP ON R.SPRT_REP_ID=RP.SPRT_REP_ID) INNER JOIN PROFILE P ON RP.PROFILE_ID=P.PROFILE_ID) INNER JOIN ACCOUNT A ON A.ACCT_GRP_ID=P.ACCT_GRP_ID) INNER JOIN ACCOUNT_MODULE AM ON A.ACCT_GRP_ID=AM.ACCT_GRP_ID) INNER JOIN MODULE M ON AM.MODULE_ID=M.MODULE_ID) WHERE DATE() BETWEEN R.EFF_DT AND NZ(R.EXP_DT,DATE()) AND DATE() BETWEEN RP.EFF_DT AND NZ(RP.EXP_DT,DATE()) AND DATE() BETWEEN P.EFF_DT AND NZ(P.EXP_DT,DATE()) AND DATE() BETWEEN A.EFF_DT AND NZ(A.EXP_DT,DATE()) AND DATE() BETWEEN AM.EFF_DT AND NZ(AM.EXP_DT,DATE()) AND DATE() BETWEEN M.EFF_DT AND NZ(M.EXP_DT,DATE()) how can i incorporate the where portion of the sql to the joins? "Bob Barrows [MVP]" wrote: joemeshuggah wrote: is it possible to join on dates? for example inner join mytbl on date() = mytbl.eff_dt and date() = nz(mytbl.exp_dt,date()) Joining on dates is certainly possible, but 1. As jeff says, a join does not seem relevant here, unless you are deliberately doing a self-join? 2. If you are joining on date/time fields that store data other than midnight, results might not be what you expect. In any case, have you tried your idea? Did it fail? What symptoms led you to believe it failed? I.E., what prompted you to ask this question? :-) -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#5
|
|||
|
|||
joining on dates
joemeshuggah wrote:
i wanted to add the date expression to the join as opposed to the where... SELECT M.MODULE_NM, R.SPRT_REP_NM, A.ACCT_GRP_NM, RP.PROFILE_ID FROM (((((REP R INNER JOIN REP_PROFILE RP ON R.SPRT_REP_ID=RP.SPRT_REP_ID) INNER JOIN PROFILE P ON RP.PROFILE_ID=P.PROFILE_ID) INNER JOIN ACCOUNT A ON A.ACCT_GRP_ID=P.ACCT_GRP_ID) INNER JOIN ACCOUNT_MODULE AM ON A.ACCT_GRP_ID=AM.ACCT_GRP_ID) INNER JOIN MODULE M ON AM.MODULE_ID=M.MODULE_ID) WHERE DATE() BETWEEN R.EFF_DT AND NZ(R.EXP_DT,DATE()) AND DATE() BETWEEN RP.EFF_DT AND NZ(RP.EXP_DT,DATE()) AND DATE() BETWEEN P.EFF_DT AND NZ(P.EXP_DT,DATE()) AND DATE() BETWEEN A.EFF_DT AND NZ(A.EXP_DT,DATE()) AND DATE() BETWEEN AM.EFF_DT AND NZ(AM.EXP_DT,DATE()) AND DATE() BETWEEN M.EFF_DT AND NZ(M.EXP_DT,DATE()) how can i incorporate the where portion of the sql to the joins? JetSQL, unlike Transact-SQL, only allows linking expressions to be part of the joins. The Rushmore query optimizer that Jet uses is smart enough to apply the criteria in the WHERE clause to limit the data from each table before it makes the joins. You can see this for yourself by turning Showplan on in the registry - see http://articles.techrepublic.com.com...1-5064388.html You could of course, use subqueries to do this, but you should be able to avoid going to that trouble ... unless Showplan reveals that Rushmore is failing to find this optimization. FROM ((((( ( SELECT SPRT_REP_ID,SPRT_REP_NM FROM REP WHERE DATE() BETWEEN EFF_DT AND NZ(EXP_DT,DATE()) ) As R ... -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
Thread Tools | |
Display Modes | |
|
|