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]
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:222320
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
