I think Keith answered your questions about my example as good or better then I
could. His example is a concrete real world example, where as mine was mostly
a pseudo code concept of an example.
I would just add one thing to try and clarify something that may be confusing
at first. There are two equally valid ways to join tables together in ones
SQL. One can do the join in the FROM clause such as Keith did with the
following example.
FROM words AS w
JOIN word_links AS wl ON w.word_id = wl.word_id
JOIN links AS l ON wl.link_id = l.link_id
Or, one can join tables in the where clause which is what I choose to do in my
example.
>FROM
> WORD_TBL, URL_TBL, WORD_URL_JOIN
>WHERE
> URL_TBL.ID = WORD_URL_JOIN.URL_ID
>AND WORD_URL_JOIN.WORD_ID = WORD_TBL.ID
>AND WORD_TBL.WORD = <cfqueryparam value="#myWordVariable#"
I asked the Presenter of the "Database Design Fundamentals" presentation at MAX
last week, and she replied the both methods are acceptable and equal in regards
to performance in any DBMS system she was familiar with. So, it is just a
choice of style on which you prefer.
To answer the one question Keith did not, as it really pertained to my choice
of the where clause join method, is that which fields are on the left or the
right of each operator (=) is irrelevant. I just did it this way as a style
choice of mine. To me this reads as URL_TBL joins to WORD_URL_JOIN joins to
WORD_TBL. But that has nothing to do with the actual syntax.
WORD_URL_JOIN.URL_ID = URL_TBL.ID AND
WORD_URL_JOIN.WORD_ID = WORD_TBL.ID AND
...
URL_TBL.ID = WORD_URL_JOIN.URL_ID AND
WORD_TBL.ID = WORD_URL_JOIN.WORD_ID
...
These both also are acceptable forms of the where joins, as well as other
combinations.
(H)ope (T)hat (H)elps :-)
--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
"C code. C code run. Run code run. Please!"
- Cynthia Dunning
Confidentiality Notice: This message including any
attachments is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the
intended recipient, please contact the sender and
delete any copies of this message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184184
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
