- - PR -
5つのテーブルをJOINする時のLEFT OUTER JOINの書き方
1
投稿者 | 投稿内容 |
---|---|
|
投稿日時: 2007-05-05 11:33
LEFT OUTER JOINの書き方がわかりません。
例えば、T1,T2,T3という3つのテーブルがあり、 T1 N:1 T2, T1 N:1 T3 という関連があります。 この3つのテーブルの結合は、 SELECT T1.STATUS, T2.TITLE AS TITLE_0, T3.BOOKID AS BOOKID_1 FROM T1 LEFT OUTER JOIN T2 t2 ON T1.T2ID = t2.ID LEFT OUTER JOIN T3 t3 ON T1.T3ID = t3.ID となります。 ところが、この3つのテーブルの他に、 T4,T5というテーブルがあり、 T3 N:1 T4, T4 N:1 T5という関連があった場合、 合計5つのテーブルを結合させる LEFT OUTER JOIN は、どう書けばいいのでしょうか? 例えば、感で書いたのですが、 SELECT T5.ID T4.USERID AS USERID_0, T2.TITLE AS TITLE_1 FROM T1 LEFT OUTER JOIN T2 t2 ON T1.T2ID = t2.ID LEFT OUTER JOIN T3 t3 ON T1.ID = t3.T1ID, T3 LEFT OUTER JOIN T4 t4 ON T3.T4ID = t4.ID, T4 LEFT OUTER JOIN T5 t5 ON T4.T5ID = t5.ID と書いたのですが、エラーになってしまいます。 ご教授いただけないでしょうか? よろしく御願いいたします。 [ メッセージ編集済み 編集者: 未記入 編集日時 2007-05-05 11:45 ] [ メッセージ編集済み 編集者: 未記入 編集日時 2007-05-05 11:50 ] |
|
投稿日時: 2007-05-05 11:47
こんにちは
で、出てきたエラーは何?、DBMSは?OSは? 文法エラーだったら ・7行目末尾の(,)コンマが邪魔 <返信している間に修正してた> テーブル名等を置き換えをしたSQLでないなら ・T1 というテーブルが FROM句に無いのに T1.〜を使用している </返信している間に修正してた> 他に >LEFT OUTER JOIN T2 t2 別名を書いている意味があるんですか? 社内標準化? [ メッセージ編集済み 編集者: 末記人 編集日時 2007-05-05 11:50 ] |
|
投稿日時: 2007-05-05 14:19
未記入大ベテランさん、お返事ありがとうございました。
>で、出てきたエラーは何?、DBMSは?OSは? H2 Database Engine Windows XP >文法エラーだったら >・7行目末尾の(,)コンマが邪魔 コンマを消して実行したが、 同じエラーがでます。 >>LEFT OUTER JOIN T2 t2 >別名を書いている意味があるんですか? >社内標準化? JavaのフレームワークであるSeasar2の Eclipse plagin, Dolteng(S2Dao)が自動生成する SQLをマネしただけなので意味はありません。 以下、Eclipseに出力されたエラー DEBUG 2007-05-05 13:31:57,078 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:31:57 078 JST(org.seasar.teeda.core.lifecycle.impl.RestoreViewPhase) DEBUG 2007-05-05 13:31:57,906 [http-8080-Processor25] クラス(book.chura.web.rentBook.RentBookPage[rentBook_rentBookPage])のコンポーネント定義を登録します DEBUG 2007-05-05 13:31:58,437 [http-8080-Processor25] クラス(book.chura.dao.BookDao[bookDao])のコンポーネント定義を登録します DEBUG 2007-05-05 13:31:58,687 [http-8080-Processor25] クラス(book.chura.dao.RentInfoDao[rentInfoDao])のコンポーネント定義を登録します DEBUG 2007-05-05 13:31:58,937 [http-8080-Processor25] クラス(book.chura.dao.UserDao[userDao])のコンポーネント定義を登録します DEBUG 2007-05-05 13:31:59,078 [http-8080-Processor25] BEGIN book.chura.web.rentBook.RentBookPage#initialize() DEBUG 2007-05-05 13:31:59,109 [http-8080-Processor25] トランザクションを開始しました DEBUG 2007-05-05 13:31:59,125 [http-8080-Processor25] トランザクションをコミットしました DEBUG 2007-05-05 13:31:59,125 [http-8080-Processor25] END book.chura.web.rentBook.RentBookPage#initialize() : null DEBUG 2007-05-05 13:31:59,906 [http-8080-Processor25] [measurement] perform ms:2828 DEBUG 2007-05-05 13:31:59,906 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:31:59 906 JST(org.seasar.teeda.core.lifecycle.impl.RestoreViewPhase) DEBUG 2007-05-05 13:31:59,906 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:31:59 906 JST(org.seasar.teeda.core.lifecycle.impl.RenderResponsePhase) DEBUG 2007-05-05 13:31:59,937 [http-8080-Processor25] BEGIN book.chura.web.rentBook.RentBookPage#prerender() DEBUG 2007-05-05 13:31:59,937 [http-8080-Processor25] トランザクションを開始しました DEBUG 2007-05-05 13:31:59,937 [http-8080-Processor25] トランザクションをコミットしました DEBUG 2007-05-05 13:31:59,937 [http-8080-Processor25] END book.chura.web.rentBook.RentBookPage#prerender() : null DEBUG 2007-05-05 13:32:00,437 [http-8080-Processor25] [measurement] perform ms:531 DEBUG 2007-05-05 13:32:00,437 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:00 437 JST(org.seasar.teeda.core.lifecycle.impl.RenderResponsePhase) DEBUG 2007-05-05 13:32:14,140 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:14 140 JST(org.seasar.teeda.core.lifecycle.impl.RestoreViewPhase) DEBUG 2007-05-05 13:32:14,328 [http-8080-Processor25] [measurement] perform ms:188 DEBUG 2007-05-05 13:32:14,328 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:14 328 JST(org.seasar.teeda.core.lifecycle.impl.RestoreViewPhase) DEBUG 2007-05-05 13:32:14,328 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:14 328 JST(org.seasar.teeda.core.lifecycle.impl.ApplyRequestValuesPhase) DEBUG 2007-05-05 13:32:14,390 [http-8080-Processor25] [measurement] perform ms:62 DEBUG 2007-05-05 13:32:14,390 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:14 390 JST(org.seasar.teeda.core.lifecycle.impl.ApplyRequestValuesPhase) DEBUG 2007-05-05 13:32:14,390 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:14 390 JST(org.seasar.teeda.core.lifecycle.impl.ProcessValidationsPhase) DEBUG 2007-05-05 13:32:14,437 [http-8080-Processor25] クラス(book.chura.web.rentBook.RentBookPage[rentBook_rentBookPage])のコンポーネント定義を登録します DEBUG 2007-05-05 13:32:14,609 [http-8080-Processor25] クラス(book.chura.dao.BookDao[bookDao])のコンポーネント定義を登録します DEBUG 2007-05-05 13:32:14,781 [http-8080-Processor25] クラス(book.chura.dao.RentInfoDao[rentInfoDao])のコンポーネント定義を登録します DEBUG 2007-05-05 13:32:14,921 [http-8080-Processor25] クラス(book.chura.dao.UserDao[userDao])のコンポーネント定義を登録します DEBUG 2007-05-05 13:32:15,015 [http-8080-Processor25] [measurement] perform ms:625 DEBUG 2007-05-05 13:32:15,015 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:15 015 JST(org.seasar.teeda.core.lifecycle.impl.ProcessValidationsPhase) DEBUG 2007-05-05 13:32:15,015 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:15 015 JST(org.seasar.teeda.core.lifecycle.impl.UpdateModelValuesPhase) DEBUG 2007-05-05 13:32:15,031 [http-8080-Processor25] [measurement] perform ms:0 DEBUG 2007-05-05 13:32:15,031 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:15 031 JST(org.seasar.teeda.core.lifecycle.impl.UpdateModelValuesPhase) DEBUG 2007-05-05 13:32:15,031 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:15 031 JST(org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase) DEBUG 2007-05-05 13:32:15,031 [http-8080-Processor25] BEGIN book.chura.web.rentBook.RentBookPage#doSubmit() DEBUG 2007-05-05 13:32:15,031 [http-8080-Processor25] トランザクションを開始しました DEBUG 2007-05-05 13:32:15,031 [http-8080-Processor25] BEGIN book.chura.dao.RentInfoDao#getAllRentInfo(null) DEBUG 2007-05-05 13:32:16,906 [http-8080-Processor25] 物理的なコネクションを取得しました DEBUG 2007-05-05 13:32:17,171 [http-8080-Processor25] 論理的なコネクションを取得しました DEBUG 2007-05-05 13:32:18,265 [http-8080-Processor25] 論理的なコネクションを閉じました DEBUG 2007-05-05 13:32:18,593 [http-8080-Processor25] select user.id, user.name, user.entryDate, user.kind, user.contact, rentInfo.limitDate AS limitDate_0, rentInfo.userId AS userId_0, bookSpec.title AS title_1 from book left outer join bookSpec bookSpec on book.bookSpecId = bookSpec.id left outer join rentInfoDetail rentInfoDetail on book. id = rentInfoDetail.bookId rentInfoDetail left outer join rentInfo rentInfo on rentInfoDetail.rentInfoId = rentInfo.id rentInfo left outer join user user on rentInfo.userId = user.id DEBUG 2007-05-05 13:32:18,593 [http-8080-Processor25] 論理的なコネクションを取得しました DEBUG 2007-05-05 13:32:18,609 [http-8080-Processor25] 物理的なコネクションを閉じました DEBUG 2007-05-05 13:32:18,625 [http-8080-Processor25] END book.chura.dao.RentInfoDao#getAllRentInfo(null) Throwable:org.seasar.framework.exception.SQLRuntimeException: [ESSR0071]SQLで例外(ErrorCode=42122, SQLState=42S22)が発生しました。理由はorg.seasar.framework.exception.SSQLException: [ESSR0072]SQLで例外(SQL=[select user.id, user.name, user.entryDate, user.kind, user.contact, rentInfo.limitDate AS limitDate_0, rentInfo.userId AS userId_0, bookSpec.title AS title_1 from book left outer join bookSpec bookSpec on book.bookSpecId = bookSpec.id left outer join rentInfoDetail rentInfoDetail on book. id = rentInfoDetail.bookId rentInfoDetail left outer join rentInfo rentInfo on rentInfoDetail.rentInfoId = rentInfo.id rentInfo left outer join user user on rentInfo.userId = user.id], ErrorCode={1}, SQLState={2})が発生しました ERROR 2007-05-05 13:32:18,765 [http-8080-Processor25] [ESSR0017]例外が発生しました。理由はorg.h2.jdbc.JdbcSQLException: The object is already closed [90007-30] org.seasar.framework.exception.SXAException: [ESSR0017]例外が発生しました。理由はorg.h2.jdbc.JdbcSQLException: The object is already closed [90007-30] at org.seasar.extension.dbcp.impl.DBXAResourceImpl.doRollback(DBXAResourceImpl.java:75) at org.seasar.extension.jta.xa.AbstractXAResource.rollback(AbstractXAResource.java:193) at org.seasar.extension.dbcp.impl.XAResourceWrapperImpl.rollback(XAResourceWrapperImpl.java:137) at org.seasar.extension.jta.XAResourceWrapper.rollback(XAResourceWrapper.java:76) at org.seasar.extension.jta.TransactionImpl.rollbackResources(TransactionImpl.java:346) at org.seasar.extension.jta.TransactionImpl.rollback(TransactionImpl.java:320) at org.seasar.extension.jta.TransactionManagerImpl.rollback(TransactionManagerImpl.java:98) at org.seasar.extension.tx.AbstractTxInterceptor.rollback(AbstractTxInterceptor.java:79) at org.seasar.extension.tx.AbstractTxInterceptor.complete(AbstractTxInterceptor.java:102) at org.seasar.extension.tx.RequiredInterceptor.invoke(RequiredInterceptor.java:47) at book.chura.web.rentBook.RentBookPage$$EnhancedByS2AOP$$5ef1eb$$MethodInvocation$$doSubmit2.proceed(MethodInvocationClassGenerator.java) at org.seasar.framework.aop.interceptors.ThrowsInterceptor.invoke(ThrowsInterceptor.java:64) at book.chura.web.rentBook.RentBookPage$$EnhancedByS2AOP$$5ef1eb$$MethodInvocation$$doSubmit2.proceed(MethodInvocationClassGenerator.java) at org.seasar.framework.aop.interceptors.TraceInterceptor.invoke(TraceInterceptor.java:59) at book.chura.web.rentBook.RentBookPage$$EnhancedByS2AOP$$5ef1eb$$MethodInvocation$$doSubmit2.proceed(MethodInvocationClassGenerator.java) at book.chura.web.rentBook.RentBookPage$$EnhancedByS2AOP$$5ef1eb.doSubmit(RentBookPage$$EnhancedByS2AOP$$5ef1eb.java) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.seasar.teeda.core.el.impl.MethodBindingImpl.invoke(MethodBindingImpl.java:70) at org.seasar.teeda.core.util.MethodBindingUtil.invoke(MethodBindingUtil.java:31) at org.seasar.teeda.core.application.ActionListenerImpl.processAction(ActionListenerImpl.java:56) at javax.faces.component.UICommand.broadcast(UICommand.java:149) at org.seasar.teeda.extension.component.html.THtmlCommandButton.broadcast(THtmlCommandButton.java:50) at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:192) at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:117) at org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase.executePhase(InvokeApplicationPhase.java:29) at org.seasar.teeda.core.lifecycle.AbstractPhase.execute(AbstractPhase.java:55) at org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase$$EnhancedByS2AOP$$154de0b.$$execute$$invokeSuperMethod$$(InvokeApplicationPhase$$EnhancedByS2AOP$$154de0b.java) at org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase$$EnhancedByS2AOP$$154de0b$$MethodInvocation$$execute0.proceed(MethodInvocationClassGenerator.java) at org.seasar.teeda.core.interceptor.MeasurementInterceptor.invoke(MeasurementInterceptor.java:46) at org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase$$EnhancedByS2AOP$$154de0b$$MethodInvocation$$execute0.proceed(MethodInvocationClassGenerator.java) at org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase$$EnhancedByS2AOP$$154de0b.execute(InvokeApplicationPhase$$EnhancedByS2AOP$$154de0b.java) at org.seasar.teeda.core.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:93) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:87) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.seasar.extension.filter.EncodingFilter.doFilter(EncodingFilter.java:62) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.seasar.framework.container.hotdeploy.HotdeployFilter.doFilter(HotdeployFilter.java:63) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.seasar.framework.container.filter.S2ContainerFilter.doFilter(S2ContainerFilter.java:63) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595) Caused by: org.h2.jdbc.JdbcSQLException: The object is already closed [90007-30] at org.h2.message.Message.getSQLException(Message.java:67) at org.h2.message.Message.getSQLException(Message.java:49) at org.h2.message.Message.getSQLException(Message.java:88) at org.h2.jdbc.JdbcConnection.checkClosed(JdbcConnection.java:1142) at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:303) at org.seasar.extension.dbcp.impl.DBXAResourceImpl.doRollback(DBXAResourceImpl.java:72) ... 58 more DEBUG 2007-05-05 13:32:18,781 [http-8080-Processor25] トランザクションをロールバックしました DEBUG 2007-05-05 13:32:18,781 [http-8080-Processor25] END book.chura.web.rentBook.RentBookPage#doSubmit() Throwable:org.seasar.framework.exception.SQLRuntimeException: [ESSR0071]SQLで例外(ErrorCode=42122, SQLState=42S22)が発生しました。理由はorg.seasar.framework.exception.SSQLException: [ESSR0072]SQLで例外(SQL=[select user.id, user.name, user.entryDate, user.kind, user.contact, rentInfo.limitDate AS limitDate_0, rentInfo.userId AS userId_0, bookSpec.title AS title_1 from book left outer join bookSpec bookSpec on book.bookSpecId = bookSpec.id left outer join rentInfoDetail rentInfoDetail on book. id = rentInfoDetail.bookId rentInfoDetail left outer join rentInfo rentInfo on rentInfoDetail.rentInfoId = rentInfo.id rentInfo left outer join user user on rentInfo.userId = user.id], ErrorCode={1}, SQLState={2})が発生しました DEBUG 2007-05-05 13:32:18,796 [http-8080-Processor25] org.seasar.framework.exception.SQLRuntimeException: [ESSR0071]SQLで例外(ErrorCode=42122, SQLState=42S22)が発生しました。理由はorg.seasar.framework.exception.SSQLException: [ESSR0072]SQLで例外(SQL=[select user.id, user.name, user.entryDate, user.kind, user.contact, rentInfo.limitDate AS limitDate_0, rentInfo.userId AS userId_0, bookSpec.title AS title_1 from book left outer join bookSpec bookSpec on book.bookSpecId = bookSpec.id left outer join rentInfoDetail rentInfoDetail on book. id = rentInfoDetail.bookId rentInfoDetail left outer join rentInfo rentInfo on rentInfoDetail.rentInfoId = rentInfo.id rentInfo left outer join user user on rentInfo.userId = user.id], ErrorCode={1}, SQLState={2})が発生しました DEBUG 2007-05-05 13:32:18,828 [http-8080-Processor25] [ESSR0071]SQLで例外(ErrorCode=42122, SQLState=42S22)が発生しました。理由はorg.seasar.framework.exception.SSQLException: [ESSR0072]SQLで例外(SQL=[select user.id, user.name, user.entryDate, user.kind, user.contact, rentInfo.limitDate AS limitDate_0, rentInfo.userId AS userId_0, bookSpec.title AS title_1 from book left outer join bookSpec bookSpec on book.bookSpecId = bookSpec.id left outer join rentInfoDetail rentInfoDetail on book. id = rentInfoDetail.bookId rentInfoDetail left outer join rentInfo rentInfo on rentInfoDetail.rentInfoId = rentInfo.id rentInfo left outer join user user on rentInfo.userId = user.id], ErrorCode={1}, SQLState={2})が発生しました DEBUG 2007-05-05 13:32:18,843 [http-8080-Processor25] [measurement] perform ms:3812 DEBUG 2007-05-05 13:32:18,843 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:18 843 JST(org.seasar.teeda.core.lifecycle.impl.InvokeApplicationPhase) DEBUG 2007-05-05 13:32:18,843 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:18 843 JST(org.seasar.teeda.core.lifecycle.impl.RestoreViewPhase) DEBUG 2007-05-05 13:32:18,984 [http-8080-Processor25] [measurement] perform ms:141 DEBUG 2007-05-05 13:32:18,984 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:18 984 JST(org.seasar.teeda.core.lifecycle.impl.RestoreViewPhase) DEBUG 2007-05-05 13:32:18,984 [http-8080-Processor25] [measurement] start date = 2007/05/05 01:32:18 984 JST(org.seasar.teeda.core.lifecycle.impl.RenderResponsePhase) DEBUG 2007-05-05 13:32:19,031 [http-8080-Processor25] [measurement] perform ms:47 DEBUG 2007-05-05 13:32:19,031 [http-8080-Processor25] [measurement] end date = 2007/05/05 01:32:19 031 JST(org.seasar.teeda.core.lifecycle.impl.RenderResponsePhase) |
|
投稿日時: 2007-05-05 14:41
エラーメッセージ中に出力されているSQLを見ると邪魔なものがあるように見えるけど...
以下ちょっと整形 select user.id, user.name, user.entryDate, user.kind, user.contact, rentInfo.limitDate AS limitDate_0, rentInfo.userId AS userId_0, bookSpec.title AS title_1 from book left outer join bookSpec bookSpec on book.bookSpecId = bookSpec.id left outer join rentInfoDetail rentInfoDetail on book.id = rentInfoDetail.bookId >>これ邪魔じゃね?>>rentInfoDetail left outer join rentInfo rentInfo on rentInfoDetail.rentInfoId = rentInfo.id >>これ邪魔じゃね?>>rentInfo left outer join user user on rentInfo.userId = user.id |
|
投稿日時: 2007-05-05 19:24
未記入大ベテランさん、お返事ありがとうございました。
ご指摘の通り訂正したら、実行できました。 ありがとうございました。 |
1