BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  sql query          BorlandTalk.com Forum Index -> Delphi Databases (Desktop) View previous topic :: View next topic   Author Message Allan Guest Posted: Wed Apr 14, 2004 11:11 pm    Post subject: sql query Hi I'm an sql newbie and having trouble getting a join to work properly. I get some data but not always the correct results so my join is wrong. tbl1 has Mytime tbl2 has MyTime and Myval I want to select all entries in tbl1.Mytime except where tbl2.Myval = x. I've tried: SELECT tbl1.Mytime from Tbl1 LEFT OUTER JOIN tbl2 ON tbl1.Mytime <> tbl2.Mytime WHERE tbl2.Myval='0' say tbl1 has Mytime 07:00 07:10 07:20 07:30 and tbl2 has Mytime Myval 07:00 0 If the value of myval is '0' in the query, the result should return records 2, 3 and 4 from tbl1. If the value of myval is '1' in the query, the result should return records 1, 2, 3 and 4 from tbl1. My query works if Myval = 0 but if any other value I get a blank result set. Can someone help me with this please? Thanks in advance. Back to top Bill Todd (TeamB) Guest Posted: Wed Apr 14, 2004 11:51 pm    Post subject: Re: sql query Do you always want all of the records from Tbl1? If not change LEFT OUTER JOIN to JOIN. -- Bill (TeamB) (TeamB cannot respond to questions received via email) Back to top Allan Guest Posted: Thu Apr 15, 2004 12:20 am    Post subject: Re: sql query Yes, I want all the records from tbl1 EXCEPT where tbl2.col1 = '0'. Re John - I did try tbl2.Myval<>0 but it returns a blank data set. My logic on this is that as there are no records in tbl2 to match the criteria, the join returns 0 records. This is where my join is wrong but I just can't figure it out, even after 2 days of scratching heads. At some stage a big 'doh' is expected. Allan "Bill Todd (TeamB)" <no (AT) no (DOT) com> wrote Quote: Do you always want all of the records from Tbl1? If not change LEFT OUTER JOIN to JOIN. -- Bill (TeamB) (TeamB cannot respond to questions received via email) Back to top Wayne Niddery [TeamB] Guest Posted: Thu Apr 15, 2004 1:03 am    Post subject: Re: sql query Allan wrote: Quote: tbl1 has Mytime tbl2 has MyTime and Myval I want to select all entries in tbl1.Mytime except where tbl2.Myval = x. I've tried: SELECT tbl1.Mytime from Tbl1 LEFT OUTER JOIN tbl2 ON tbl1.Mytime <> tbl2.Mytime WHERE tbl2.Myval='0' Reverse the conditions. You want to *match* the entries between the two tables, and then eliminate the ones you don't want using the Where clause: SELECT tbl1.Mytime from Tbl1 LEFT OUTER JOIN tbl2 ON tbl1.Mytime = tbl2.Mytime WHERE tbl2.Myval <> '0' If there is always at least on matching record in tbl2 for each record in tbl1, or you only want tbl1 records where there is such a match, then use an INNER join instead of LEFT OUTER. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Bill Todd (TeamB) Guest Posted: Thu Apr 15, 2004 1:27 am    Post subject: Re: sql query Then it sounds like Wayne's solution is what you want. -- Bill (TeamB) (TeamB cannot respond to questions received via email) Back to top Allan Guest Posted: Thu Apr 15, 2004 6:02 am    Post subject: Re: sql query Hi all Thanks for the help but it still doesn't work properly. Maybe I haven't described what I am trying to do well enough. If I was to describe it in a sequence, it would be: join tbl1 and tbl2 together remove any entries where myval = 0 make Mytime DISTINCT So my results were (in tbl2, Myval = 0) (result set should be: 07:10, 07:20, 07:30) LEFT OUTER JOIN <> = result set is: 07:10, 07:20, 07:30 so far so good but if the value of Myval in tbl2 is changed........ (in tbl2, Myval = 1) (result set should be 07:00, 07:10, 07:20, 07:30) LEFT OUTER JOIN <> = result set is: empty set so it doesn't work when Myval is set to a non zero value in the tbl2. Please help as I am still really lost here on what looks like a simple query but is causing me to rip my hair out! Thanks in advance Allan "Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote Quote: Allan wrote: tbl1 has Mytime tbl2 has MyTime and Myval I want to select all entries in tbl1.Mytime except where tbl2.Myval = x. I've tried: SELECT tbl1.Mytime from Tbl1 LEFT OUTER JOIN tbl2 ON tbl1.Mytime <> tbl2.Mytime WHERE tbl2.Myval='0' Reverse the conditions. You want to *match* the entries between the two tables, and then eliminate the ones you don't want using the Where clause: SELECT tbl1.Mytime from Tbl1 LEFT OUTER JOIN tbl2 ON tbl1.Mytime = tbl2.Mytime WHERE tbl2.Myval <> '0' If there is always at least on matching record in tbl2 for each record in tbl1, or you only want tbl1 records where there is such a match, then use an INNER join instead of LEFT OUTER. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Wayne Niddery [TeamB] Guest Posted: Fri Apr 16, 2004 12:19 am    Post subject: Re: sql query Allan wrote: Quote: Thanks for the help but it still doesn't work properly. Maybe I haven't described what I am trying to do well enough. If I was to describe it in a sequence, it would be: join tbl1 and tbl2 together remove any entries where myval = 0 make Mytime DISTINCT So my results were (in tbl2, Myval = 0) (result set should be: 07:10, 07:20, 07:30) LEFT OUTER JOIN <> = result set is: 07:10, 07:20, 07:30 so far so good Actually no, you happen to be getting the right result for the wrong reason. Making a join using "<>" makes no sense (at least 99.9% of the time). Maybe I missed it but I don't recall you saying you needed Distinct results before. However, given the example data you provided, the results should be distinct anyway. What is the exact relationship between the two tables? Are records always one to one or is it one to many in one direction or the other (e.g. given one record in tbl1, how many matching records could there be in tbl2?). Is it possible for a record in tbl1 to not have any matches in tbl2? If there can be more than one record in tbl2 matching a record in tbl1, do you want all matches or do you only want to see each unique time once? -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Allan Guest Posted: Fri Apr 16, 2004 7:42 am    Post subject: Re: sql query Wayne, thanks for your time, really appreciate it. It must be frustrating sometimes trying to answer questions that are not clear and only provide a glimpse of what I was trying to do. I finally worked it out today by tossing away the join. First, I have to get records in tbl1 that don't match some criteria in tbl2 and then UNION this result with specific records I want in tbl2. The following gives me what I need. However I would be interested if there is a neater way of doing this. SELECT mytime FROM tbl1 WHERE mytime <> ALL (SELECT mytime from tbl2 where myval = '0' AND mytime = '04/22/2004') UNION SELECT mytime FROM tbl2 WHERE myval <> '0' AND mytime = '04/22/2004' order by mytime Again, thanks for your time. Back to top Wayne Niddery [TeamB] Guest Posted: Sat Apr 17, 2004 12:30 am    Post subject: Re: sql query Allan wrote: Quote: First, I have to get records in tbl1 that don't match some criteria in tbl2 Ok, this is what I wasn't getting and it explains why you were trying to use <> in the join clause. Quote: and then UNION this result with specific records I want in tbl2. The following gives me what I need. However I would be interested if there is a neater way of doing this. SELECT mytime FROM tbl1 WHERE mytime <> ALL (SELECT mytime from tbl2 where myval = '0' AND mytime = '04/22/2004') UNION SELECT mytime FROM tbl2 WHERE myval <> '0' AND mytime = '04/22/2004' order by mytime Depending on the exact relationship (multiplicity) between these tables, there *may* be a better way. E.g. the way to do what you were attempting with the join would be this pattern: select mytime from tbl1 t1 left outer join tbl2 t2 on t2.mytime = t1.mytime and t2.myval = '0' where t2.mytime is null You still need the union but I think this should work for the first part and, assuming it does, might be faster. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Allan Guest Posted: Sat Apr 17, 2004 8:49 am    Post subject: Re: sql query Thanks for this, I'll give it a go. As to query speed, what are the considerations in evaluating the speed of a query? Are some ways, eg a join, faster than another eg union? Anywhere on the web where there is some info? Is there a way to test different queries for speed? Quote: You still need the union but I think this should work for the first part and, assuming it does, might be faster. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Wayne Niddery [TeamB] Guest Posted: Sat Apr 17, 2004 9:17 pm    Post subject: Re: sql query Allan wrote: Quote: As to query speed, what are the considerations in evaluating the speed of a query? Are some ways, eg a join, faster than another eg union? Anywhere on the web where there is some info? Is there a way to test different queries for speed? Depends on the database you are using, if it is an SQL database like InterBase or MSSQL then there are tools you can get to help measure performance or even help design the SQL statements. All products have different performance characteristics - doing some things better or worse than other products. *In general* the SQL-92 join syntax (inner/left/right join) can be parsed and optimized easier by the server and so gives better performance than using other methods of joining tables. But joins can't do everything, you still need unions, sub-selects, etc. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Allan Guest Posted: Tue Apr 20, 2004 5:56 am    Post subject: Re: sql query Hi Wayne Some reading I have come across states that when joining tables the sets should be as small as possible for optimal sql times. Is a join slower than a "not in" given that a join requires merging of tables and a "not in" is an exclusion from a result? e.g. select mytime from tbl1 t1 left outer join tbl2 t2 on t2.mytime = t1.mytime and t2.myval = '0' where t2.mytime is null or select t1.mytime from tbl1 t1 where t1.mytime not in (select t2.mytime from tbl2 t2 where t2.myval ='0') Thanks in advance Allan "There are no shortcuts to success, only hard work" - Anon Back to top Wayne Niddery [TeamB] Guest Posted: Tue Apr 20, 2004 11:52 pm    Post subject: Re: sql query Allan wrote: Quote: Some reading I have come across states that when joining tables the sets should be as small as possible for optimal sql times. Of course smaller sets will run faster, that does not mean joins are slow. Quote: Is a join slower than a "not in" given that a join requires merging of tables and a "not in" is an exclusion from a result? That really depends on the database you are using, but in general joins are going to be faster because they will use available indexes (implying of course that you must be sure to create the correct indexes). Quote: select t1.mytime from tbl1 t1 where t1.mytime not in (select t2.mytime from tbl2 t2 where t2.myval ='0') If the database is able to optimize this to use an index, it may end up being as good as the join, but you would realy have to try both to see - using a query analyzer for the database you are using (for Interbase, use Planalyzer). -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "True peace is not the absence of tension, but the presence of justice." - Martin Luther King, Jr. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (Desktop) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.