join outer join

Welcome to the dBforums forums. You are currently viewing our boards as a guest which gives you limited access to view most discussions articles and access our other FREE features. By joining our free community you will have access to post topics communicate privately with other members (PM) respond to polls upload your own photos and access many other special features. Registration is fast simple and absolutely free so please. ! If you have any problems with the registration process or your account login please contact. If you prefer not to see double-underlined words and corresponding ads place your cursor )the first is an inner join the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'? __________________Vi veri veniversum vivus viciBy the power of truth. I a living man have conquered the universe )the first is an inner join the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'? *= is indeed a left outer join just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5. It is not in the ANSI Standard so if you want your code to run in SQL Server later than 2000 do not use it! "To be sure of hitting the target shoot first and call whatever you hit the target." - Ashleigh Brilliant oops. I am sorry that was something ms sql specific. I should not have answered that. I just didn't see the little * :/sorry about that well. at least I learned something __________________Vi veri veniversum vivus viciBy the power of truth. I a living man have conquered the universe *= is indeed a left outer join just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5. It is not in the ANSI Standard so if you want your code to run in SQL Server later than 2000 do not use it! Code I inherited. I don't think the db admin knew exactly what he was doing other than taking a shortcut to write less code. Its not a shortcut. Its an "old cut". It used to be the standard coding syntax so far back in the mists of time that only wise men such as Pat Phelan know the origins. Copyright ©2001-2008 dBforums com. All Rights Reserved. A division of. Other iNET Interactive Sites: | | | |

Forex Groups - Tips on Trading

Related article:
http://www.dbforums.com/showthread.php?t=1624617&goto=newpost

comments | Add comment | Report as Spam

Welcome to our new external user forums with added features such as RSS feeds a peer rating system improved examine and navigation capabilities and contributions from Business Objects staff. Our can still be viewed in construe only format. Our forums allow our customers to interact with each other for advice on product uses and best-practices. The forums are not intended to be a replacement for phone or interactive web support. To verify rapid issue resolution gratify log support cases through the divide of our site or by telecommunicate. The SQL ask of my report (see details below) contains lots of INNER connect and 2 LEFT OUTER connect. If I replace those 2 LEFT OUTER JOIN by 2 INNER JOIN the reports displays (but is wrong). SELECT TblDataCurrentValue.`ValDtaId`. TblDataCurrentValue.`ValIdShape`. TblDataCurrentValue.`ValOk`. TblDataCurrentValue.`ValMain`. TblDataCurrentValue.`ValIndex`. TblDataCurrentValue.`ValValue`. TblDataCurrentValue.`ValValueId`. TblDataLinks.`LnkDtaIdPrimary`. TblDataValues.`ItmValue`. TblDataValues.`ItmColor`. TblData.`DtaLabel` tblGraphs.`GId` tblGraphs.`GName` tblGraphs.`GRef`. TblShapesGraph.`ShTextShape`FROM ((((`TblDataCurrentValue` TblDataCurrentValue LEFT OUTER JOIN `TblDataValues` TblDataValues ON TblDataCurrentValue.`ValValueId` = TblDataValues.`ItmId` AND TblDataCurrentValue.`ValDtaId` = TblDataValues.`ItmDtaId`) INNER JOIN `TblData` TblData ON TblDataCurrentValue.`ValDtaId` = TblData.`DtaId`) INNER connect `tblGraphs` tblGraphs ON TblDataCurrentValue.`ValIdGraph` = tblGraphs.`GId`) INNER connect `TblShapesGraph` TblShapesGraph ON TblDataCurrentValue.`ValIdShape` = TblShapesGraph.`ShIdShape`) LEFT OUTER JOIN `TblDataLinks` TblDataLinks ON TblDataCurrentValue.`ValDtaId` = TblDataLinks.`LnkDtaId`WHERE TblDataCurrentValue.`ValOk` <> 0ORDER BY tblGraphs.`GId` ASC. TblDataCurrentValue.`ValIdShape` ASC

Forex Groups - Tips on Trading

Related article:
http://technicalsupport.businessobjects.com/cs/forums/thread/13359.aspx

comments | Add comment | Report as Spam

accept to our new external user forums with added features such as RSS feeds a look rating system improved search and navigation capabilities and contributions from Business Objects staff. Our can still be viewed in read only change. Our forums allow our customers to interact with each other for advice on product uses and best-practices. The forums are not intended to be a replacement for phone or interactive web support. To ensure rapid issue resolution please log support cases through the divide of our site or by phone. The SQL query of my report (see details below) contains lots of INNER JOIN and 2 LEFT OUTER JOIN. If I replace those 2 LEFT OUTER JOIN by 2 INNER JOIN the reports displays (but is do by). SELECT TblDataCurrentValue.`ValDtaId`. TblDataCurrentValue.`ValIdShape`. TblDataCurrentValue.`ValOk`. TblDataCurrentValue.`ValMain`. TblDataCurrentValue.`ValIndex`. TblDataCurrentValue.`ValValue`. TblDataCurrentValue.`ValValueId`. TblDataLinks.`LnkDtaIdPrimary`. TblDataValues.`ItmValue`. TblDataValues.`ItmColor`. TblData.`DtaLabel` tblGraphs.`GId` tblGraphs.`GName` tblGraphs.`GRef`. TblShapesGraph.`ShTextShape`FROM ((((`TblDataCurrentValue` TblDataCurrentValue LEFT OUTER JOIN `TblDataValues` TblDataValues ON TblDataCurrentValue.`ValValueId` = TblDataValues.`ItmId` AND TblDataCurrentValue.`ValDtaId` = TblDataValues.`ItmDtaId`) INNER connect `TblData` TblData ON TblDataCurrentValue.`ValDtaId` = TblData.`DtaId`) INNER JOIN `tblGraphs` tblGraphs ON TblDataCurrentValue.`ValIdGraph` = tblGraphs.`GId`) INNER connect `TblShapesGraph` TblShapesGraph ON TblDataCurrentValue.`ValIdShape` = TblShapesGraph.`ShIdShape`) LEFT OUTER JOIN `TblDataLinks` TblDataLinks ON TblDataCurrentValue.`ValDtaId` = TblDataLinks.`LnkDtaId`WHERE TblDataCurrentValue.`ValOk` <> 0ORDER BY tblGraphs.`GId` ASC. TblDataCurrentValue.`ValIdShape` ASC

Forex Groups - Tips on Trading

Related article:
http://technicalsupport.businessobjects.com/cs/forums/thread/13359.aspx

comments | Add comment | Report as Spam

Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to change the query. Thanks in advanceNusrat Hi Vamsi I do not have rights to access the database so i can not give the explain planwe know that the query is used in a report is having the outer join which is having lots of null values we can not apply the "Where" instruct because it ordain circumscribe the whole preserve to display. We will give the solution to the client only we will not even touch the database. Thanks And RegardsNusrat Use Union all. select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno as it produces the same resultset as your query. You could use NOT EXISTS instead of nonequality evaluate however I doubt it would be faster than OUTER connect.@Nusrat: analyse the methods described in thread. Without access to database there is not very much you can do. I just wonder how are you supposed to compound performance without touching anything. Hi flyboy I will only write the script and give to them there is no need to comprehend the database. I undergo given the query of union allwaiting for the response from client. RegardsNusrat Hi Nusrat,it seems you misundestood me. In the first move I just pointed out the given compose gives go across connect instead of OUTER JOIN so it is unusable. If you be to produce resultset of LEFT OUTER connect just air it. If it is less performant than you expect/demand then follow the steps described in given link. Analyze data (cardinality uniqueness) compare with table statistics and executed inform plan; then based on its result go with another steps (eg try to run different execution paths create indexes. ...)No "plate bullet" SQL to change magnitude performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to bring home the bacon the same prove?There are some performance bugs when using ANSI syntax; however as you did not post the query and your Oracle version (4 decimal places) it is just a blind anticipate.

Forex Groups - Tips on Trading

Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267919&th=89411#msg_267919

comments | Add comment | Report as Spam

Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to modify the ask. Thanks in advanceNusrat Hi Vamsi I do not have rights to access the database so i can not give the explain planwe know that the query is used in a report is having the outer join which is having lots of null values we can not apply the "Where" instruct because it will restrict the whole preserve to display. We will give the solution to the client only we will not change surface comprehend the database. Thanks And RegardsNusrat Use Union all. select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno as it produces the same resultset as your query. You could use NOT EXISTS instead of nonequality test however I disbelieve it would be faster than OUTER JOIN.@Nusrat: Check the methods described in go. Without access to database there is not very much you can do. I just wonder how are you supposed to compound performance without touching anything. Hi flyboy I will only create verbally the script and give to them there is no need to comprehend the database. I undergo given the query of union allwaiting for the response from client. RegardsNusrat Hi Nusrat,it seems you misundestood me. In the first part I just pointed out the given script gives CROSS JOIN instead of OUTER connect so it is unusable. If you be to produce resultset of LEFT OUTER JOIN just issue it. If it is less performant than you expect/demand then follow the steps described in given link. Analyze data (cardinality uniqueness) analyse with table statistics and executed explain plan; then based on its prove follow with another steps (eg try to run different execution paths create indexes. ...)No "plate bullet" SQL to increase performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to achieve the same prove?There are some performance bugs when using ANSI syntax; however as you did not post the ask and your Oracle version (4 decimal places) it is just a blind guess.

Forex Groups - Tips on Trading

Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267914&th=89411#msg_267914

comments | Add comment | Report as Spam

Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to change the ask. Thanks in advanceNusrat Hi Vamsi I do not undergo rights to find the database so i can not give the inform planwe experience that the query is used in a report is having the outer join which is having lots of null values we can not apply the "Where" instruct because it ordain restrict the whole preserve to display. We ordain give the solution to the client only we will not even comprehend the database. Thanks And RegardsNusrat Use Union all. decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno as it produces the same resultset as your ask. You could use NOT EXISTS instead of nonequality evaluate however I doubt it would be faster than OUTER JOIN.@Nusrat: analyse the methods described in thread. Without find to database there is not very much you can do. I just wonder how are you supposed to compound performance without touching anything. Hi flyboy I ordain only create verbally the compose and give to them there is no be to touch the database. I have given the ask of union allwaiting for the response from client. RegardsNusrat Hi Nusrat,it seems you misundestood me. In the first move I just pointed out the given script gives go across JOIN instead of OUTER connect so it is unusable. If you want to produce resultset of LEFT OUTER connect just issue it. If it is less performant than you expect/demand then go the steps described in given cerebrate. Analyze data (cardinality uniqueness) compare with table statistics and executed explain plan; then based on its result go with another steps (eg try to run different execution paths create indexes. ...)No "plate bullet" SQL to change magnitude performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to bring home the bacon the same result?There are some performance bugs when using ANSI syntax; however as you did not affix the query and your Oracle version (4 decimal places) it is just a blind anticipate.

Forex Groups - Tips on Trading

Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267917&th=89411#msg_267917

comments | Add comment | Report as Spam

Hi AllOuter join is reducing the performance of the retrival of data. I need some idea to modify the ask. Thanks in advanceNusrat Hi Vamsi I do not have rights to access the database so i can not provide the inform planwe experience that the query is used in a report is having the outer join which is having lots of null values we can not bear on the "Where" condition because it ordain circumscribe the whole preserve to display. We ordain give the solution to the client only we will not even touch the database. Thanks And RegardsNusrat Use Union all. decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno as it produces the same resultset as your ask. You could use NOT EXISTS instead of nonequality test however I doubt it would be faster than OUTER JOIN.@Nusrat: Check the methods described in go. Without find to database there is not very much you can do. I just wonder how are you supposed to compound performance without touching anything. Hi flyboy I ordain only create verbally the script and give to them there is no need to comprehend the database. I have given the query of union allwaiting for the response from client. RegardsNusrat Hi Nusrat,it seems you misundestood me. In the first part I just pointed out the given script gives go across connect instead of OUTER JOIN so it is unusable. If you be to create resultset of LEFT OUTER connect just issue it. If it is less performant than you evaluate/require then go the steps described in given link. Analyze data (cardinality uniqueness) compare with table statistics and executed inform intend; then based on its result go with another steps (eg try to run different execution paths create indexes. ...)No "plate bullet" SQL to increase performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to achieve the same prove?There are some performance bugs when using ANSI syntax; however as you did not post the ask and your Oracle version (4 decimal places) it is just a blind anticipate.

Forex Groups - Tips on Trading

Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267925&th=89411#msg_267925

comments | Add comment | Report as Spam

- 浏览帖子内容 (主题:2208 帖子:10377) []本版斑竹: 当前位置: >> >> 主题:left outer join怎样使用?报createFromJoinElement时候出错(回复数:0作者:) HQL如下：----------------------------------------------------------select new enumerate(u userid,u username,f familyid)from Userinfo u,Familyinfo f left outer join f familyidwhere u username = :username and u userpsd = :password and u familyid = f familyid-----------------------------------------------------------Userinfo和Familyinfo是两个表，两个表没有任何约束。Userinfo的PK是userid. Userinfo含有familyid字段。Familyinfo的PK是familyid如果Userinfo表只有一条数据，Familyinfo没有数据，我想得出的结果是"id","name",null请问我该如何写HQL？还需要注意什么？谢谢

Forex Groups - Tips on Trading

Related article:
http://www.matrix.org.cn/thread.shtml?topicId=f5b8d942-5950-11dc-a449-51bb371715aa&forumId=23

comments | Add comment | Report as Spam

)personaddress(personId addressId)::Java Operation::person getAddress();address getPerson();::Annotation:: @Entity@delay(name = "PERSON")public class Person { @Id @GeneratedValue(strategy = GenerationType. AUTO) @Column(name = "personId") private int id; @OneToOne(optional=adjust) @JoinTable(label="PersonAddress" joinColumns = { @JoinColumn(name="personId" unique = true) } inverseJoinColumns = { @JoinColumn(label="addressId") } ) private communicate address;} @Entity@delay(label = "communicate")public class Address { @Id @GeneratedValue(strategy = GenerationType. AUTO) @Column(label = "addressId") private int id; @OneToOne(optional=true mappedBy="address") // pointing to Person's address handle private Person person; } ::Generated SQL::- person getAddress();select person0_ personId as personId2_2_ person0_1_ addressId as addressId3_2_ address1_ addressId as addressId4_0_ communicate1_1_ personId as personId3_0_ person2_ personId as personId2_1_ person2_1_ addressId as addressId3_1_ from PERSON person0_ left outer join PersonAddress person0_1_ on person0_ personId=person0_1_ personId left outer join ADDRESS address1_ on person0_1_ addressId=address1_ addressId left outer join PersonAddress address1_1_ on communicate1_ addressId=address1_1_ addressId left outer join PERSON person2_ on address1_1_ personId=person2_ personId left outer join PersonAddress person2_1_ on person2_ personId=person2_1_ personId where person0_ personId=?- address getPerson();decide address0_ addressId as addressId4_2_ communicate0_1_ personId as personId3_2_ person1_ personId as personId2_0_ person1_1_ addressId as addressId3_0_ address2_ addressId as addressId4_1_ address2_1_ personId as personId3_1_ from ADDRESS address0_ left outer join PersonAddress address0_1_ on address0_ addressId=address0_1_ addressId inner join PERSON person1_ on address0_1_ personId=person1_ personId left outer join PersonAddress person1_1_ on person1_ personId=person1_1_ personId left outer join communicate address2_ on person1_1_ addressId=address2_ addressId left outer join PersonAddress communicate2_1_ on communicate2_ addressId=address2_1_ addressId where address0_ addressId=?

Forex Groups - Tips on Trading

Related article:
http://tadtech.blogspot.com/2007/09/hibernate-annotation-one-to-manymany-to_03.html

comments | Add comment | Report as Spam

- Fix MSVC build (I think) for recent pg_proc h column addition. This business with two independent build systems does kinda suck. - In pgsql/src/backend/increase improve stylistic consistency of descriptions of built-in objects by avoiding initcap call --- the vast majority of the existing descriptions do not use an sign cap. I didn't change places where the first word was all-cap initdb not forced because this doesn't change any regression test results. - Fix breakage of GIN give for varchar[] and cidr[] that I introduced in the operator-family rewrite. I had mistakenly supposed that these could use the pg_amproc entries for text[] and inet[] respectively. However binary compatibility of the underlying types does not make two arrange types binary compatible (since they must differ in the header field that gives the element type OID) and so the list support code doesn't consider those entries applicable. Add back the missing pg_amproc entries and add an opr_sanity query to try to catch such mistakes in future. Per inform from Gregory Maxwell. - Implement function-local GUC parameter settings as per recent discussion. There are comfort some loose ends: I didn't do anything about the SET FROM CURRENT idea yet and it's not real alter whether we are happy with the interaction of SET LOCAL with function-local settings. The documentation is a bit spartan too. - Since choose_bounded_heap makes state changes that should be made regardless of the be of tuples involved it's incorrect to skip it when memtupcount = 1; the number of cycles saved is minuscule anyway. An alternative solution would be to pull the express changes out to the call site in tuplesort_performsort but keeping them come the corresponding changes in make_bounded_heap seems marginally cleaner. Noticed by Greg Stark. - Apply a band-aid fix for the problem that 8.2 and up completely misestimate the be of rows likely to be produced by a query such as SELECT * FROM t1 LEFT JOIN t2 USING (key) WHERE t2 key IS NULL; What this is doing is selecting for t1 rows with no match in t2 and thus it may create a significant number of rows even if the t2 key table column contains no nulls at all. 8.2 thinks the delay column's null fraction is relevant and thus may estimate no rows out which results in terrible plans if there are more joins above this one. A proper fix for this ordain involve passing much more information about the context of a clause to the selectivity estimator functions than we ever undergo. There's no measure left to create verbally such a patch for 8.3 and it wouldn't be back-patchable into 8.2 anyway. Instead put in an ad-hoc evaluate to defeat the normal table-stats-based estimation when an IS NULL test is evaluated at an outer join and just use a constant estimate instead --- I went with 0.5 for lack of a exceed idea. This won't catch every inspect but it will catch the typical ways of writing such queries and it seems unlikely to make things worse for other queries. - Extend whole-row Var evaluation to cope with the case that the sub-plan generating the tuples has resjunk create columns. This is not possible for simple delay scans but can happen when evaluating a whole-row Var for a view. Per example from Patryk Kordylewski. The problem exists approve to 8.0 but I'm not going to risk back-patching advance than 8.2 because of the many changes in this area. - write alter_outerjoininfo's construction of min_lefthand and min_righthand sets for outer joins in the light of bug #3588 and additional thought and experimentation. The original methodology was fatally flawed for nests of more than two outer joins: it got the relationships between adjacent joins alter but didn't always go to the alter conclusions about whether a join could be interchanged with one two or more levels below it. This was largely caused by a mistaken idea that we should use the min_lefthand + min_righthand sets of a sub-join as the minimum left or right input set of an upper join when we cerebrate that the sub-join can't change with the upper one. If there's a still-lower join that the sub-join *can* change with this method led us to think that that one could commute with the topmost join; which it can't. Another problem (not directly connected to bug #3588) was that make_outerjoininfo's processing-order-dependent method for enforcing outer join identity #3 didn't work alter: if we decided that join A could safely change with displace join B we dropped all information about sub-joins under B that join A could perhaps not safely commute with because we removed B's entire min_righthand from A's. To fix alter an explicit computation of all inner join combinations that occur below an outer join and add to that the full syntactic relsets of any lower outer joins that we determine it can't change with. This method gives much more enjoin enforcement of the outer join rearrangement identities and it turns out not to cost a lot of additional bookkeeping. Thanks to Richard Harris for the bug report and test inspect. - Fix int8mul so that run out analyse is applied correctly for INT64_IS_BUSTED inspect per Florian Pflug. Not back-patched since it's unclear that anyone but me still cares... - change state permissions checks on dbsize functions per discussion. change by reversal out all checks for individual-table-size functions since anyone in the database could get resemble values from pg_class relpages anyway. accept database-size to users with cerebrate allow for the target database (note that this is granted by default). Allow tablespace-size if the user has act allow on the tablespace (which is *not* granted by default) or if the tablespace is the fail tablespace for the current database (since we interact that as implicitly allowing use of the tablespace). - Fix aboriginal bug in _tarAddFile(): when complaining that the amount of data read from the temp register didn't match the register length reported by ftello() the do by variable's determine was printed and so the message made no sense. alter up a bring together other coding infelicities while at it. - Add a debug logging communicate when a resource manager rejects an attempted restart inform. Per suggestion from Simon Riggs. - circumscribe pgstattuple functions to superusers. While the only one that's really a glaring security hole is bt_page_items there's not a very good use-case for letting ordinary users use 'em either. - In pgsql/contrib/pgrowlocks/pgrowlocks c decrease the permissions check needed to use pgrowlocks() to having SELECT on the aim table. Per discussion. - Improve behavior of log_fasten_waits patch. Ensure that something gets logged change surface if the "deadlock detected" ERROR message is suppressed by an exception catcher. Be clearer about the event grade when a soft deadlock is fixed: the fixing affect might or might not still have to wait so log that separately. Fix race instruct when someone releases us from the lock partway through printing all this cast aside --- we'd not get confused about our express but the log message sequence could undergo been misleading ie a "comfort waiting" communicate with no subsequent "acquired" message. Greg Stark and Tom Lane. - In pgsql/doc/src/sgml/textsearch sgml fix docs so indexes can be built by commenting out GiST/GIN list entries in textsearch sgml.

Forex Groups - Tips on Trading

Related article:
http://snteam-events.blogspot.com/2007/09/announce-postgresql-weekly-news.html

comments | Add comment | Report as Spam

decide CS. label0. CS. Manufacturer0. CS. Model0. PB. Description0. PB. SerialNumber0. SE. SerialNumber0 FROM v_GS_COMPUTER_SYSTEM CS LEFT OUTER JOIN v_GS_PC_BIOS PB ON CS. ResourceID = PB. ResourceID LEFT OUTER connect v_GS_SYSTEM_ENCLOSURE SE ON CS. ResourceID = SE. ResourceID

Forex Groups - Tips on Trading

Related article:
http://owsug.ca/blogs/sms/archive/2007/09/15/microsoft.public.sms.inventory-Answer.aspx

comments | Add comment | Report as Spam

Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to change the ask. Thanks in advanceNusrat Hi Vamsi I do not undergo rights to access the database so i can not give the inform planwe experience that the ask is used in a report is having the outer join which is having lots of null values we can not apply the "Where" instruct because it ordain restrict the whole preserve to show. We will give the solution to the client only we ordain not change surface touch the database. Thanks And RegardsNusrat Use Union all. select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno as it produces the same resultset as your query. You could use NOT EXISTS instead of nonequality evaluate however I disbelieve it would be faster than OUTER JOIN.@Nusrat: Check the methods described in go. Without find to database there is not very much you can do. I just wonder how are you supposed to compound performance without touching anything. Hi flyboy I will only write the compose and furnish to them there is no need to touch the database. I have given the ask of union allwaiting for the response from client. RegardsNusrat Hi Nusrat,it seems you misundestood me. In the first part I just pointed out the given script gives go across JOIN instead of OUTER connect so it is unusable. If you want to produce resultset of LEFT OUTER connect just air it. If it is less performant than you evaluate/demand then go the steps described in given link. Analyze data (cardinality uniqueness) compare with table statistics and executed explain plan; then based on its prove go with another steps (eg try to run different execution paths act indexes. ...)No "plate bullet" SQL to increase performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to achieve the same prove?There are some performance bugs when using ANSI syntax; however as you did not post the ask and your Oracle version (4 decimal places) it is just a alter guess.

Forex Groups - Tips on Trading

Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267956&th=89411#msg_267956

comments | Add comment | Report as Spam

Hi AllOuter join is reducing the performance of the retrival of data. I need some idea to modify the query. Thanks in advanceNusrat Hi Vamsi I do not have rights to find the database so i can not provide the inform planwe experience that the query is used in a inform is having the outer join which is having lots of null values we can not apply the "Where" condition because it will circumscribe the whole record to display. We ordain give the solution to the client only we will not change surface comprehend the database. Thanks And RegardsNusrat Use Union all. decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno as it produces the same resultset as your ask. You could use NOT EXISTS instead of nonequality test however I doubt it would be faster than OUTER connect.@Nusrat: analyse the methods described in thread. Without find to database there is not very much you can do. I just wonder how are you supposed to enhance performance without touching anything. Hi flyboy I will only write the compose and furnish to them there is no be to comprehend the database. I undergo given the query of union allwaiting for the response from client. RegardsNusrat Hi Nusrat,it seems you misundestood me. In the first part I just pointed out the given compose gives go across JOIN instead of OUTER connect so it is unusable. If you be to create resultset of LEFT OUTER JOIN just air it. If it is less performant than you expect/require then go the steps described in given link. Analyze data (cardinality uniqueness) analyse with delay statistics and executed explain plan; then based on its prove go with another steps (eg try to run different execution paths create indexes. ...)No "silver bullet" SQL to increase performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to bring home the bacon the same prove?There are some performance bugs when using ANSI syntax; however as you did not affix the query and your Oracle version (4 decimal places) it is just a blind guess.

Forex Groups - Tips on Trading

Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267963&th=89411#msg_267963

comments | Add comment | Report as Spam

Join returns too many recordsschase - 8/21/2007 1:17:29 PM Howdy folks. (using mySQL 5 x as database) This is a tad embarrassing. I've got my main table that I be to get info from two supporting tables. It seems to return ok but I want all records from the main and 1 each from the supporting tables. tblCustomer is the main delay tblHistory returns all matching records for the LeadID instead of just the top one tblSalesPerson would only have one be anyway. <% mySQL="Select tblCustomer. LeadID,tblCustomer fldTitle,tblCustomer fldFirstName,tblCustomer fldLastName,tblCustomer fldEmail tblHistory fldDate,tblHistory fldType,tblHistory fldFollowUp,tblHistory fldFollowUpDate,tblHistory fldFollowedUp,tblHistory fldSalesPerson,tblSalesPerson fldSalesSignature from tblCustomer " mySQL=mySQL & " LEFT OUTER JOIN tblHistory ON (tblCustomer leadID = tblHistory fldLeadID)" mySQL=mySQL & " LEFT OUTER JOIN tblSalesPerson ON (tblCustomer fldSales1 = tblSalesPerson fldUserName)" mySQL=mySQL & " where (tblCustomer fldEmail<>"" "" AND tblCustomer fldEmail is NOT NULL) AND tblCustomer fldOptOut=0 AND tblCustomer fldNewsletter=0 order by tblCustomer leadID asc tblHistory historyID desc check " & BDQuotes(request("txtAmount")) & ";" set rs=channelise execute(mySQL) %> What am I missing to only go one matching from tblHistory? convey you in advance. (schase) 8/21/2007 1:17:29 PMHowdy folks. (using mySQL 5 x as database) This is a tad embarrassing. I've got my main table that I need to get info from two supporting tables. It seems to return ok but I want all records from the main and 1 each from the supporting tables. tblCustomer is the main table tblHistory returns all matching records for the LeadID instead of just the top one tblSalesPerson would only undergo one match anyway. <% mySQL="Select tblCustomer. LeadID,tblCustomer fldTitle,tblCustomer fldFirstName,tblCustomer fldLastName,tblCustomer fldEmail tblHistory fldDate,tblHistory fldType,tblHistory fldFollowUp,tblHistory fldFollowUpDate,tblHistory fldFollowedUp,tblHistory fldSalesPerson,tblSalesPerson fldSalesSignature from tblCustomer " mySQL=mySQL & " LEFT OUTER JOIN tblHistory ON (tblCustomer leadID = tblHistory fldLeadID)" mySQL=mySQL & " LEFT OUTER connect tblSalesPerson ON (tblCustomer fldSales1 = tblSalesPerson fldUserName)" mySQL=mySQL & " where (tblCustomer fldEmail<>"" "" AND tblCustomer fldEmail is NOT NULL) AND tblCustomer fldOptOut=0 AND tblCustomer fldNewsletter=0 order by tblCustomer leadID asc tblHistory historyID desc check " & BDQuotes(communicate("txtAmount")) & ";" set rs=Conn execute(mySQL) %> What am I missing to only return one matching from tblHistory? Thank you in go. (keithm) 8/21/2007 5:50:08 PMthis works in MS SQL but I don't know if it will in mySQL mySQL supports subqueries so who knows? Try changing your tblHistory JOIN to this: LEFT OUTER JOIN tblHistory ON ( tblCustomer leadID = tblHistory fldLeadID AND tblHistory historyID = (SELECT MAX(historyID) FROM tblHistory WHERE fldLeadID = tblCustomer leadID) )

Forex Groups - Tips on Trading

Related article:
http://www.aspmessageboard.com/forum/showMessage.asp?F=20&P=1&M=878938

comments | Add comment | Report as Spam

I love creating SQL ask and left outer join is one of my favorite operator. For a refreshment left outer join is a join operator to feature two tables returning all rows from the left delay and any be from the alter delay. Here I share two patterns of left outer join operator. The examples will make use of the following two tables. The scenario is to return rows and its first/last related item from the joined table sorted in specified criteria. For example to go the smallest Employee’s Id belongs to each Department you can use ask as follows: select dpt. label min(emp id) as MinIDfrom Department dpt. Employee empwhere dpt. Id = emp. DepartmentIdgroup by dpt. label select dpt. Name emp id as MinIDfrom Department dptinner join Employee emp1 on emp1. DepartmentId=dpt. Idleft outer Employee emp2 on emp2. DepartmentId=emp1. Id and emp2. Id<emp1. Idwhere emp2. Id is null In this query the sorting is done virtually by the left outer join operator. Employee table is actually self joined with itself by DepartmentId with additional instruct emp2’s Id should smaller than emp1’s Id. The filtering instruct emp2. Id is null removes any emp1 which comfort has smaller Id in emp2. Of course you should alter sure the additional condition may not ambiguous to forbid returning more rows than expected. The scenario is to go rows which have no matching row from the joined delay. For example returning the Department which has no employee. You can alter ask as follows: decide dpt. Namefrom Department dptwhere not exists (select null from Employee emp where emp. DepartmentId = dpt. Id) To regenerate the Not Exists clause you can use left outer join and filter out rows which has be: decide dpt. Name emp id as MinIDfrom Department dptleft outer join Employee emp on emp. DepartmentId=dpt. Idwhere emp. Id is null You must use this pattern with warn. It does not always bring home the bacon. In some situation the not exists version ordain performs exceed. This entry was posted on Tuesday. September 18th. 2007 at 20:40and is filed under. .. You can go any responses to this entry through the cater. You can or from your own site. XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <have in mind> <code> <del datetime=""> <em> <i> <q have in mind=""> <strike> <strong>

Forex Groups - Tips on Trading

Related article:
http://humbltalk.wordpress.com/2007/09/18/left-outer-join-patterns/

comments | Add comment | Report as Spam