RE: SQL prob - a reason other than style
There's a good reason to use the new style of joins: because they are more powerful. Here is an example of a query that, to my knowledge, can't be done with *= syntax: select * from t1 left outer join t2 on (t1.id = t2.id and t1.rating < t2.max_rating) This is very different from: select * from t1 left outer join t2 on (t1.id = t2.id) where t1.rating < t2.max_rating .so it must be different than the *= style This kind of complex outer join is what "educated" me to the new style of joins, and I've used them ever since. (And they read better too.) Mark ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201560 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
I would ask him to give it back - this syntax is deprecated and could well be removed at any stage from SQL Server . David Manriquez [SMTP:[EMAIL PROTECTED] -Original Message- From: David Manriquez To: CF-Talk Sent: 05/04/2005 16:28 Subject: RE: SQL prob I'm still using it cause a Microsoft Certified SQL professional show me the tracing and performance of "LEFT/RIGHT/OUTHER/INNER JOIN and *=/=/=*/*=* and the last one is better and faster. David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Matt Osbun [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 11:18 Para: CF-Talk Asunto: RE: SQL prob Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201554 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
There's a good reason to use the new style of joins: because they are more powerful. Here is an example of a query that, to my knowledge, can't be done with *= syntax: select * from t1 left outer join t2 on (t1.id = t2.id and t1.rating < t2.max_rating) This is very different from: select * from t1 left outer join t2 on (t1.id = t2.id) where t1.rating < t2.max_rating so it must be different than the *= style This kind of complex outer join is what "educated" me to the new style of joins, and I've used them ever since. (And they read better too.) -Original Message- From: Matt Osbun [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 11:18 AM To: CF-Talk Subject: RE: SQL prob Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201553 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
Matt Osbun wrote: > Why? Not a challenge, but a question. Using *= or (+) always seemed, > at least to me, quicker, easier, and easier to read afterward. > > Now, I admit that I mostly got into the habit because, until fairly > recently, I was stuck on an Oracle platform that was too old to use > INNER JOIN/LEFT JOIN, but how much does it really matter? Ask 10 developers that got their BSc in CS during the last 12 months to explain both syntaxes. Extrapolate that to three or five years in the future. Will your replacement still be able to read your code? *= and (+) are deprecated, legacy, on their way out. They may very well last 4 more decades in some obscure COBOL application, but just as you should use IsDefined instead of ParameterExists, you should use LEFT JOIN syntax. Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201544 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
> I don't feel that this sort of because-you-might-not-be-smart-enough > type of argument is a good reason to do anything one way or another. I don't think that Dave was saying people shouldn't use "*=" because they might not be smart enough. I think that good developers like Dave recognize error-prone code and avoid it because they accept that they aren't perfect and look for less error-prone alternatives (especially when the less-error-prone alternative is a standard). > My argument on these type of things is always along the lines of "do it > the way it works and performs best and if someone else isn't > smart/good/experienced enough to 'get it' then they should learn more." A purely spaghetti-code app may work (functionallty) and perform better than an OO-alternative, but the OO approach seems to have caught on nicely. There are tradeoffs to be made. -Joe -- Get Glued! The Model-Glue ColdFusion Framework http://www.model-glue.com ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201524 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
> For two, specifically speaking, I can point to a couple > instances where I've annoyed the heck out of a DBA by > forgetting to join a couple of million+ record tables before > running the query. I can think of a million ways a dba's annoyed me :) sorry, I'll get back to work -- dc ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201523 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
Can't say I really took it as a "You-Might-Not-Be-Smart-Enough" sort of thing. And even if it was, it really wouldn't bother me much. For one, generally speaking, I'm pretty careful about my table joins. For two, specifically speaking, I can point to a couple instances where I've annoyed the heck out of a DBA by forgetting to join a couple of million+ record tables before running the query. Point is, mistakes happen and if a particular method can help prevent them, that's worth keeping in mind. Should it be the only concern? Certainly not, but other factors being more or less equal, I'm all for something that will keep a minor mistake into becoming a larger problem. Anyway, thanks for the responses, all. Matt -Original Message- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 11:33 AM To: CF-Talk Subject: RE: SQL prob Once again, someone pops in with this reason for doing something. It troubles me that this manner of thinking is so prevalent, as it most certainly means it's come about from actual experiences dealing with people. I don't feel that this sort of because-you-might-not-be-smart-enough type of argument is a good reason to do anything one way or another. I'm not taking a shot at you here Dave, far from it, but it seems that every time someone asks "why shouldn't I do x this way," someone points out that a good reason has to do with the fact that people won't be able to do it right or won't be able to understand... Are people getting LESS capable out there or are we just accepting their (I should say "our" to include myself or just drop the possessive altogether) inadequacies more willingly or what? On the other hand, I suppose we could be ascribing inadequacies to people unfairly, though I rather doubt it. My argument on these type of things is always along the lines of "do it the way it works and performs best and if someone else isn't smart/good/experienced enough to 'get it' then they should learn more." Does this bother anyone else, or is it just me? It could easily be just me; I've had it up to here with people in general this morning. If it is, maybe I should just hit the power button and take the rest of the day. --Ferg -Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 11:18 AM To: CF-Talk Subject: RE: SQL prob One advantage of explicit JOIN syntax is that it eliminates the possibility of an accidental Cartesian product or cross join. If you have a query with three or four tables joined within the WHERE clause, you might forget one of your joins and the query will run successfully, but will fetch a Cartesian product (which you typically don't want, and which will typically annoy your DBA). If you use explicit JOIN syntax, you will get an error message if you forget a join, which is a much better result. Personally, I prefer explicit JOIN syntax because I find it a bit easier to read, although admittedly I used to find the old syntax easy to read as well. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201521 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
> Once again, someone pops in with this reason for doing > something. It troubles me that this manner of thinking is so > prevalent, as it most certainly means it's come about from > actual experiences dealing with people. > > I don't feel that this sort of because-you-might-not-be-smart-enough > type of argument is a good reason to do anything one way or > another. I'm not taking a shot at you here Dave, far from it, > but it seems that every time someone asks "why shouldn't I do > x this way," someone points out that a good reason has to do > with the fact that people won't be able to do it right or > won't be able to understand... Are people getting LESS > capable out there or are we just accepting their (I should > say "our" to include myself or just drop the possessive > altogether) inadequacies more willingly or what? On the other > hand, I suppose we could be ascribing inadequacies to people > unfairly, though I rather doubt it. > > My argument on these type of things is always along the lines > of "do it the way it works and performs best and if someone > else isn't smart/good/experienced enough to 'get it' then > they should learn more." > > Does this bother anyone else, or is it just me? It could > easily be just me; I've had it up to here with people in > general this morning. If it is, maybe I should just hit the > power button and take the rest of the day. I don't think this reason has anything to do with not being smart enough. No matter how good a programmer you are, everyone makes mistakes, and one of the traits of a good programmer is to be able to identify and recover from those mistakes as quickly as possible - we call this debugging! Now, with this specific example, the advantage that I pointed out is that if you make the specific mistake of overlooking a join, you'll get an error instead of a Cartesian product, and thus you (and your database server and your DBA) will be able to clearly identify the problem and fix it more quickly. After all, if you're working with enough tables, you might not even immediately identify that you have a problem if you fetch a Cartesian product. If there were any clear performance implications of choosing one syntax over the other, that would be a viable argument for choosing that syntax, but in my experience it simply doesn't affect performance beyond a negligible amount. Even if the old syntax did generally perform better than the new syntax, you might be better off choosing the new syntax if it results in clearer and more maintainable code. If you were to take your argument to its logical conclusion, we should clearly all be using C++ at a minimum, or perhaps even assembler, since those will generally perform better than using a high-level, loosely-typed scripting language that is compiled into bytecode that is then run within a virtual machine that runs within a real machine. But we don't, since hardware costs less than skilled programmers' time. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201521 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
> From: Ken Ferguson [mailto:[EMAIL PROTECTED] > Does this bother anyone else, or is it just me? It could > easily be just me; I've had it up to here with people in > general this morning. If it is, maybe I should just hit the > power button and take the rest of the day. Click that button... ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201518 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
Once again, someone pops in with this reason for doing something. It troubles me that this manner of thinking is so prevalent, as it most certainly means it's come about from actual experiences dealing with people. I don't feel that this sort of because-you-might-not-be-smart-enough type of argument is a good reason to do anything one way or another. I'm not taking a shot at you here Dave, far from it, but it seems that every time someone asks "why shouldn't I do x this way," someone points out that a good reason has to do with the fact that people won't be able to do it right or won't be able to understand... Are people getting LESS capable out there or are we just accepting their (I should say "our" to include myself or just drop the possessive altogether) inadequacies more willingly or what? On the other hand, I suppose we could be ascribing inadequacies to people unfairly, though I rather doubt it. My argument on these type of things is always along the lines of "do it the way it works and performs best and if someone else isn't smart/good/experienced enough to 'get it' then they should learn more." Does this bother anyone else, or is it just me? It could easily be just me; I've had it up to here with people in general this morning. If it is, maybe I should just hit the power button and take the rest of the day. --Ferg -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 11:18 AM To: CF-Talk Subject: RE: SQL prob One advantage of explicit JOIN syntax is that it eliminates the possibility of an accidental Cartesian product or cross join. If you have a query with three or four tables joined within the WHERE clause, you might forget one of your joins and the query will run successfully, but will fetch a Cartesian product (which you typically don't want, and which will typically annoy your DBA). If you use explicit JOIN syntax, you will get an error message if you forget a join, which is a much better result. Personally, I prefer explicit JOIN syntax because I find it a bit easier to read, although admittedly I used to find the old syntax easy to read as well. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201517 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
Also, instead of running the queries (not a very valid test unless you do each way many times under similar conditions), try looking at estimated execution plans, etc. You'll probably notice that in a lot of cases the optimizer (in SQL server, at least) will create the same execution plan for either way. -Joe -- Get Glued! The Model-Glue ColdFusion Framework http://www.model-glue.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201515 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
I don't think one test can be seen as proof of either way being better or faster - there's a lot more that goes into DB performance than JOIN vs. =. Indexes, constraints, what mood your OS is in, etc. True story: a company I worked for had to fire a Microsoft Certified Professional (actually, an MCSE) after watching him try to install a 3Com NIC into a Windows 2k box for two days. Not to say there aren't good ones, but I've a shifty amount of trust ever since. -Joe On Apr 5, 2005 11:28 AM, David Manriquez <[EMAIL PROTECTED]> wrote: > I'm still using it cause a Microsoft Certified SQL professional show me the > tracing and performance of "LEFT/RIGHT/OUTHER/INNER JOIN and *=/=/=*/*=* > and the last one is better and faster. > > > David Manriquez > Desarrollador de Sistemas > [EMAIL PROTECTED] > (+56-2) 43 00 155 > > -Mensaje original- > De: Matt Osbun [mailto:[EMAIL PROTECTED] > Enviado el: Martes, 05 de Abril de 2005 11:18 > Para: CF-Talk > Asunto: RE: SQL prob > > Why? Not a challenge, but a question. Using *= or (+) always seemed, > at least to me, quicker, easier, and easier to read afterward. > > Now, I admit that I mostly got into the habit because, until fairly > recently, I was stuck on an Oracle platform that was too old to use > INNER JOIN/LEFT JOIN, but how much does it really matter? > > Matt Osbun > Web Developer > Health Systems, International > > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 05, 2005 9:42 AM > To: CF-Talk > Subject: RE: SQL prob > > This is the OLD way to perform a join - it should be avoided. > > -Original Message- > From: David Manriquez [mailto:[EMAIL PROTECTED] > Sent: 05 April 2005 15:48 > To: CF-Talk > Subject: RE: SQL prob > > Another way > > SELECT r.eventID, d.title > FROM Events r,EventDescription d > WHERE d.eventID *= r.eventID > > David Manriquez > Desarrollador de Sistemas > [EMAIL PROTECTED] > (+56-2) 43 00 155 > > -Mensaje original- > De: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Enviado el: Martes, 05 de Abril de 2005 10:17 > Para: CF-Talk > Asunto: RE: SQL prob > > You are using an INNER JOIN - you need to use a LEFT JOIN I believe. > > SELECT r.eventID, d.title > FROM Events r > LEFT JOIN EventDescription d > ON WHERE d.eventID = r.eventID > > -Original Message- > From: Dave Francis [mailto:[EMAIL PROTECTED] > Sent: 05 April 2005 15:18 > To: CF-Talk > Subject: OT:SQL prob > > SQL Server 7. > > > SELECT r.eventID, d.title >FROM Events r, > EventDescription d > WHERE d.eventID = r.eventID > > > This query only returns rows from "Events" table that have eventID > present in "EventDescription" table. I need to return ALL rows from > Events table whether thay have a description or not. > > As ever, thanks in advance, > Dave > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201511 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
I don't think one test can be seen as proof of either way being better or faster - there's a lot more that goes into DB performance than JOIN vs. =. Indexes, constraints, what mood your OS is in, etc. True story: a company I worked for had to fire a Microsoft Certified Professional (actually, an MCSE) after watching him try to install a 3Com NIC into a Windows 2k box for two days. Not to say there aren't good ones, but I've a shifty amount of trust ever since. -Joe ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201513 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
> Why? Not a challenge, but a question. Using *= or (+) > always seemed, at least to me, quicker, easier, and easier to > read afterward. > > Now, I admit that I mostly got into the habit because, until > fairly recently, I was stuck on an Oracle platform that was > too old to use INNER JOIN/LEFT JOIN, but how much does it > really matter? One advantage of explicit JOIN syntax is that it eliminates the possibility of an accidental Cartesian product or cross join. If you have a query with three or four tables joined within the WHERE clause, you might forget one of your joins and the query will run successfully, but will fetch a Cartesian product (which you typically don't want, and which will typically annoy your DBA). If you use explicit JOIN syntax, you will get an error message if you forget a join, which is a much better result. Personally, I prefer explicit JOIN syntax because I find it a bit easier to read, although admittedly I used to find the old syntax easy to read as well. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201510 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
Indeed. Plus I was always under the impression that all other things being equal, explicity stating your joins would be faster. Seems to make sense logically too. >> From: David Manriquez [mailto:[EMAIL PROTECTED] >> I'm still using it cause a Microsoft Certified SQL >> professional show me the tracing and performance of >> "LEFT/RIGHT/OUTHER/INNER JOIN and *=/=/=*/*=* and the last >> one is better and faster. > > A lot more goes into it than that... Indexes, keys, etc all play a role in > how efficient your joins are... > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201509 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
> From: David Manriquez [mailto:[EMAIL PROTECTED] > I'm still using it cause a Microsoft Certified SQL > professional show me the tracing and performance of > "LEFT/RIGHT/OUTHER/INNER JOIN and *=/=/=*/*=* and the last > one is better and faster. A lot more goes into it than that... Indexes, keys, etc all play a role in how efficient your joins are... ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201508 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
I'm still using it cause a Microsoft Certified SQL professional show me the tracing and performance of "LEFT/RIGHT/OUTHER/INNER JOIN and *=/=/=*/*=* and the last one is better and faster. David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Matt Osbun [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 11:18 Para: CF-Talk Asunto: RE: SQL prob Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201507 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL prob
Hey Matt, >From a semantic (easier / easier to read standpoint), using the (currently) standard JOIN sytax seperates your query nicely. When you use it, the FROM clause becomes where all your relations are defined, and the WHERE clause simply becomes filtering on everything you've JOINed together. -Joe On Apr 5, 2005 11:18 AM, Matt Osbun <[EMAIL PROTECTED]> wrote: > Why? Not a challenge, but a question. Using *= or (+) always seemed, > at least to me, quicker, easier, and easier to read afterward. > > Now, I admit that I mostly got into the habit because, until fairly > recently, I was stuck on an Oracle platform that was too old to use > INNER JOIN/LEFT JOIN, but how much does it really matter? > > Matt Osbun > Web Developer > Health Systems, International > > -Original Message- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 05, 2005 9:42 AM > To: CF-Talk > Subject: RE: SQL prob > > This is the OLD way to perform a join - it should be avoided. > > -Original Message- > From: David Manriquez [mailto:[EMAIL PROTECTED] > Sent: 05 April 2005 15:48 > To: CF-Talk > Subject: RE: SQL prob > > Another way > > SELECT r.eventID, d.title > FROM Events r,EventDescription d > WHERE d.eventID *= r.eventID > > David Manriquez > Desarrollador de Sistemas > [EMAIL PROTECTED] > (+56-2) 43 00 155 > > -Mensaje original- > De: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Enviado el: Martes, 05 de Abril de 2005 10:17 > Para: CF-Talk > Asunto: RE: SQL prob > > You are using an INNER JOIN - you need to use a LEFT JOIN I believe. > > SELECT r.eventID, d.title > FROM Events r > LEFT JOIN EventDescription d > ON WHERE d.eventID = r.eventID > > -Original Message- > From: Dave Francis [mailto:[EMAIL PROTECTED] > Sent: 05 April 2005 15:18 > To: CF-Talk > Subject: OT:SQL prob > > SQL Server 7. > > > SELECT r.eventID, d.title >FROM Events r, > EventDescription d > WHERE d.eventID = r.eventID > > > This query only returns rows from "Events" table that have eventID > present in "EventDescription" table. I need to return ALL rows from > Events table whether thay have a description or not. > > As ever, thanks in advance, > Dave > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201506 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
I should add - deprecated in SQL Server. -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 16:17 To: CF-Talk Subject: RE: SQL prob Why - because it is deprecated. -Original Message- From: Matt Osbun [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 16:18 To: CF-Talk Subject: RE: SQL prob Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201504 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
I should add - deprecated in SQL Server... -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 16:17 To: CF-Talk Subject: RE: SQL prob Why - because it is deprecated. -Original Message- From: Matt Osbun [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 16:18 To: CF-Talk Subject: RE: SQL prob Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201502 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
Why - because it is deprecated. -Original Message- From: Matt Osbun [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 16:18 To: CF-Talk Subject: RE: SQL prob Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201501 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
Why? Not a challenge, but a question. Using *= or (+) always seemed, at least to me, quicker, easier, and easier to read afterward. Now, I admit that I mostly got into the habit because, until fairly recently, I was stuck on an Oracle platform that was too old to use INNER JOIN/LEFT JOIN, but how much does it really matter? Matt Osbun Web Developer Health Systems, International -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:42 AM To: CF-Talk Subject: RE: SQL prob This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201500 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
This is the OLD way to perform a join - it should be avoided. -Original Message- From: David Manriquez [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:48 To: CF-Talk Subject: RE: SQL prob Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201496 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
Another way SELECT r.eventID, d.title FROM Events r,EventDescription d WHERE d.eventID *= r.eventID David Manriquez Desarrollador de Sistemas [EMAIL PROTECTED] (+56-2) 43 00 155 -Mensaje original- De: Robertson-Ravo, Neil (RX) [mailto:[EMAIL PROTECTED] Enviado el: Martes, 05 de Abril de 2005 10:17 Para: CF-Talk Asunto: RE: SQL prob You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201494 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
You are using an INNER JOIN - you need to use a LEFT JOIN I believe. SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON WHERE d.eventID = r.eventID -Original Message- From: Dave Francis [mailto:[EMAIL PROTECTED] Sent: 05 April 2005 15:18 To: CF-Talk Subject: OT:SQL prob SQL Server 7. SELECT r.eventID, d.title FROM Events r, EventDescription d WHERE d.eventID = r.eventID This query only returns rows from "Events" table that have eventID present in "EventDescription" table. I need to return ALL rows from Events table whether thay have a description or not. As ever, thanks in advance, Dave ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201493 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL prob
> From: Dave Francis [mailto:[EMAIL PROTECTED] > SELECT r.eventID, d.title > FROM Events r, > EventDescription d > WHERE d.eventID = r.eventID SELECT r.eventID, d.title FROM Events r LEFT JOIN EventDescription d ON (r.eventID = d.eventID) HTH, Mike ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201490 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
