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'.
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:222306
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
