Thanks for that JP, that helped me too. I'll try and make the
gradual change-over to the standard sql-92.
On 26 Oct 2005, at 15:47, John Paul Ashenfelter wrote:
> On 10/26/05, Charlie Hanlon <[EMAIL PROTECTED]> wrote:
>
>> John Paul,
>> You stated
>>
>> "I'd *always* use the SQL standard of explictly stating the joins
>> instead of
>> just letting the db figure it out"....
>>
>> Is this a best practice that I should adopt. I am self-taught and
>> am not
>> aware that explictly joining the tables is more efficient than
>> joining the
>> tables in the 'Where' clause'.
>>
>
> Short answer is yes, you should. While most databases accept the older
> format of just listing the tables in the FROM clause, you have a lot
> more flexibility with the SQL-92 syntax of
> {INNER|LEFT|RIGHT|FULL|OUTER|CROSS} JOIN that specifically sets what
> columns/criteria you're joining *on*. That standard's been around
> since, well, 1992, so its rare to find a db that doesn't support it.
>
> Not only is it easier to read what's going on (for example, you'd have
> to explicitly do a CROSS JOIN to get the results you were getting --
> which would be a dead giveaway that somethings wrong) but you can also
> do non-equi joins if you need to and the db supports it (non-equijoins
> allow you to join using two columns and an expression, like <10
> instead of just the = sign).
>
> Under the hood, the SQL parser in your database will translate two
> equivalent queries to the same execution plan, so it's not "more
> efficient" to use this syntax per se, but it is more standard, more
> readable, and more flexible -- and less prone to errors as your
> problems shows :)
>
>
>> thanks in advance for your clarification/confirmation...
>>
>> rgds,
>> Charlie Hanlon
>>
>>
>>
>> ----- Original Message -----
>> From: "John Paul Ashenfelter" <[EMAIL PROTECTED]>
>> To: "CF-Talk" <[email protected]>
>> Sent: Wednesday, October 26, 2005 9:59 AM
>> Subject: Re: Simple SQL
>>
>>
>>
>>> On 10/26/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>>>
>>>> Hi guys,
>>>>
>>>> I'm trying to do a simple query but am having a blank and pulling
>>>> multiples.
>>>>
>>>> <!--- get all event details and their corresponding
>>>> music genre type --->
>>>> <cfquery datasource="user020" name="GetEvents">
>>>> SELECT e.eventID, e.eventName, e.eventTeaser,
>>>> e.eventDate, m.musicGenreID, m.musicGenreName
>>>> FROM tbl_020eventDetails e, tbl_020musicGenres m
>>>> WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
>>>> ORDER BY e.eventDate ASC
>>>> </cfquery>
>>>>
>>>> What i'd like it to do is pull each event and the corresponding
>>>> music
>>>> genre but instead each record is getting pulled multiple times all
>>>> with the same musicGenreID details.
>>>>
>>>
>>> Are you getting a cross join? Sounds like it -- I don't see what
>>> relates the tables in your query.
>>>
>>> I'd *always* use the SQL standard of explictly stating the joins
>>> instead of just letting the db figure it out
>>>
>>> SELECT e.eventID, e.eventName, e.eventTeaser, e.eventDate,
>>> m.musicGenreID, m.musicGenreName
>>> FROM tbl_020eventDetails e,
>>> INNER JOIN tbl_020musicGenres m ON (whatever relates the tables)
>>> WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
>>> ORDER BY e.eventDate ASC
>>>
>>> --
>>> John Paul Ashenfelter
>>> CTO/Transitionpoint
>>> (blog) http://www.ashenfelter.com
>>> (email) [EMAIL PROTECTED]
>>>
>>>
>>>
>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:222330
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
