Category:#cat_info.category#
#Artist_Name#
#album#
Listen In Real Audio
This Item is in stock and ready to ship to you.

#format# #DollarFormat(price)#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => StylusDesigns [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 13 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Select data From 3 Linked Tables - Macromedia ColdFusion

Select data From 3 Linked Tables - Macromedia ColdFusion

I had a select statement that was able to get info from 2 tables and disply it correctly then I went back and made some changes so now I need to select from 3 tables go get my info. Here is my select statement: <cfquery name="get_albums" datasource="klaritymusic"> SELECT A.id AS artist_id, AL.id AS album_id, A.artist_name as artist_name, AL.album as album, AL.cover_image as cover_image AF.format as format AF.price as price AF.shopcartid as shopcartid FROM Artist A, Album AL, avail_formats AF WHERE A.ID = AL.Artist_ID AND AL.Cat_ID = #url.Cat_ID# AND AL.ID = AF.Album_ID </cfquery> The Error message is: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC ...

  1. #1

    Default Select data From 3 Linked Tables

    I had a select statement that was able to get info from 2 tables and disply it
    correctly then I went back and made some changes so now I need to select from 3
    tables go get my info. Here is my select statement:

    <cfquery name="get_albums" datasource="klaritymusic">
    SELECT
    A.id AS artist_id,
    AL.id AS album_id,
    A.artist_name as artist_name,
    AL.album as album,
    AL.cover_image as cover_image
    AF.format as format
    AF.price as price
    AF.shopcartid as shopcartid
    FROM Artist A, Album AL, avail_formats AF
    WHERE A.ID = AL.Artist_ID
    AND AL.Cat_ID = #url.Cat_ID#
    AND AL.ID = AF.Album_ID
    </cfquery>

    The Error message is:

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver] The SELECT statement includes a reserved word or an argument
    name that is misspelled or missing, or the punctuation is incorrect.

    The error occurred in D:\Sites\ecsmaine.com\wwwroot\klaritymusic\categor y.cfm:
    line 20

    18 : FROM Artist A, Album AL, avail_formats AF
    19 : WHERE A.ID = AL.Artist_ID
    20 : AND AL.Cat_ID = #url.Cat_ID#
    21 : AND AL.ID = AF.Album_ID
    22 : </cfquery>

    Thanks



    StylusDesigns Guest

  2. #2

    Default Re: Select data From 3 Linked Tables

    For openers, you need single quotes: '#url.Cat_ID#'
    jdeline Guest

  3. #3

    Default Re: Select data From 3 Linked Tables

    Added the quotes, got the same quotes.
    StylusDesigns Guest

  4. #4

    Default Re: Select data From 3 Linked Tables

    Added the quotes, got the same error.
    StylusDesigns Guest

  5. #5

    Default Re: Select data From 3 Linked Tables

    If the data type of [Album].[Cat_ID] is "numeric", you do not need to use
    single quotes around #url.Cat_ID#

    You're missing a few commas after the column names in the SELECT statement:

    AL.album as album,
    AL.cover_image as cover_image <-- add comma
    AF.format as format <-- add comma
    AF.price as price <-- add comma
    AF.shopcartid as shopcartid
    FROM Artist A, Album AL, avail_formats AF

    Also, you may want to consider using CFQUERYPARAM for security reasons

    mxstu Guest

  6. #6

    Default Re: Select data From 3 Linked Tables

    Thanks, I didn't see the comma's missing.

    I did end up finding a solution though, I went into ms access and used the
    query wizard then copied the sql from that and it worked great.

    Now...CFQUERYPARAM how & why would I use that in this case? I am always
    interested in added security.

    StylusDesigns Guest

  7. #7

    Default Re: Select data From 3 Linked Tables

    Yes, sometimes that is a good santity check.

    See [url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm[/url] (or the version 7 docs)
    mxstu Guest

  8. #8

    Default Re: Select data From 3 Linked Tables

    I can get my data now, but I cannot get it to layout correctly. I have 5 tables
    I am getting data from; categories, albums, artists, tracks, and avail_formats.
    I am not sure if I can do multiple queries, to get the data but I'd rather do
    it all in one shot for speed.

    I could not think of a way to have them more combined because of the all of
    the different combo posibilities of one to many relationships.

    an artist can be in multiple categories, an artist will have multiple albums
    which may be in different categories, each album will have multiple tracks,
    some with sample tracks some without, and some albums are availble in multiple
    formats, cd or cassette, or maybe dvd and vhs.

    Anyways...here is my query

    <cfquery name="get_albums" datasource="klaritymusic">

    SELECT
    artist.id AS artist_id,
    album.id AS album_id,
    artist.artist_name,
    album.album,
    album.cover_image,
    avail_formats.format,
    avail_formats.price,
    avail_formats.shopcartid AS shopcartid,
    album.cat_id
    FROM artist
    INNER JOIN (album
    INNER JOIN avail_formats ON album.id = avail_formats.album_id)
    ON artist.id = album.artist_id
    WHERE
    (((album.cat_id)=#url.cat_id#));

    </cfquery>

    Then when it outputs if I have more then one format in the avail_format table
    that album is listed twice in on the page. I tried using another cfoutput with
    grouping for to get the add to cart buttons but it didn't work.

    Here is the code for the output

    <table cellpadding="2" cellspacing="0" width="750" align="center"
    bgcolor="ffffff" border="0">
    <cfoutput query="get_albums" group="Artist_Name">
    <tr><td colspan="3" class="blue_cat"><A
    HREF="artist_details.cfm?artist_id=#artist_id#"
    CLASS="cat_title">#Artist_Name#</A></td></tr>
    <cfoutput group="album">
    <tr>
    <td width="80" valign="top"><a
    href="album_detail.cfm?cat_id=#cat_info.id#&album_ id=#album_id#"
    class="cat_title"><img src="images/albumcovers/thumbnails/thumb_#cover_image#"
    width="80" height="80" border="0"></a></td>
    <td width="395" valign="top"><a
    href="album_detail.cfm?cat_id=#cat_info.id#&album_ id=#album_id#"
    class="cat_title">#album#</a>
    <br><a href="" class="cat_title">Listen In Real Audio</a> <IMG
    SRC="images/speaker2.gif" WIDTH="11" HEIGHT="14" BORDER="0" ALT=""
    align="middle"></td>
    <TD align="right" height="10" valign="top" width="275"><B
    class="normal">This Item
    is in stock and ready to ship to you.</B><br>
    <BR>
    <B class="bold">
    <B CLASS="cat_label">#format#</B>&nbsp;<B
    CLASS="cat_price">#DollarFormat(price)# </B>
    <A
    HREF="http://www.1shoppingcart.com/app/netcart.asp?merchantid=8216&amp;productID
    =#shopcartid#" CLASS="cat_add"><IMG ALT="" BORDER="0" HEIGHT="21"
    SRC="images/add_cart.gif" WIDTH="93" align="middle"></A>
    <br>
    </TD>
    <tr><td colspan="3" height="6" valign="middle"><img
    src="images/dashed_line.gif" width="750" height="2"></td></tr>
    </tr>
    </cfoutput>
    </cfoutput>
    </table>



    StylusDesigns Guest

  9. #9

    Default Re: Select data From 3 Linked Tables

    insert another cfoutput, without a grouping attribute, around the format display. I've found in the past you just need to mess with the cfoutput tags and the grouping to get what your looking for.
    esch Guest

  10. #10

    Default Re: Select data From 3 Linked Tables

    I have been messing around with that but no luck yet.

    StylusDesigns Guest

  11. #11

    Default Re: Select data From 3 Linked Tables

    In the code you posted, you are outputting the query named "get_albums".
    Within the output there is:
    <a href="album_detail.cfm?cat_id=#cat_info.id#&album_ id=#album_id#"
    class="cat_title">#album#</a>

    What is "cat_info.id" ? Is that another query?

    mxstu Guest

  12. #12

    Default Re: Select data From 3 Linked Tables

    The cat_info.id is from another query, but I won't need that other query, I wasn't pulling that info in my huge query at first because I didn't know how.


    StylusDesigns Guest

  13. #13

    Default Re: Select data From 3 Linked Tables

    well....I left it and came back a few days later and managed to get what I
    needed.

    I have attached the code.

    <cfquery name="cat_info" datasource="klaritymusic">
    SELECT id,category FROM category where id = #url.cat_id#
    </cfquery>
    <cfquery name="get_albums" datasource="klaritymusic">
    SELECT
    album.artist_id,
    artist.id AS artist_id,
    artist.artist_name,
    artist.active AS artist_active,
    avail_formats.id AS avail_formats_id,
    avail_formats.format,
    avail_formats.price,
    avail_formats.shopcartid,
    avail_formats.album_id,
    album.cat_id,
    album.cover_image,
    album.item_number,
    album.active AS album_active,
    album.album,
    album.id AS album_id
    FROM artist INNER JOIN (album INNER JOIN avail_formats ON album.id =
    avail_formats.album_id) ON artist.id = album.artist_id
    WHERE (((artist.active)='Yes') AND ((album.cat_id)= #url.cat_id#) AND
    ((album.active)='Yes'))
    ORDER BY artist.artist_name, album.album, avail_formats.format;

    </cfquery>
    <!--MAIN CONTENT START-->
    <cfoutput query="cat_info">
    <TABLE class="white" width="800" border="0" cellpadding="4" cellspacing="0">
    <TR>
    <TD align="left" class="artist" colspan="3">
    <table cellspacing="0" cellpadding="0" class="white">
    <tr><td class="artist" width="80">Category:</td><td class="artist"><A
    HREF="category.cfm?cat_id=#cat_info.id#">#cat_info .category#</A></td></tr>
    </table>
    </TD>
    </TR>
    </cfoutput>
    <TD class="light" align="middle">
    <table cellpadding="2" cellspacing="0" width="750" align="center"
    bgcolor="ffffff" border="0">
    <cfoutput query="get_albums" group="artist_name">
    <cfoutput group="album">
    <tr><td colspan="3" class="blue_cat"><A
    HREF="artist_details.cfm?artist_id=#artist_id#"
    CLASS="cat_title">#Artist_Name#</A></td></tr>
    <tr>
    <td width="80" valign="top"><a
    href="album_detail.cfm?cat_id=#cat_info.id#&album_ id=#album_id#"
    class="cat_title"><img src="images/albumcovers/thumbnails/thumb_#cover_image#"
    width="80" height="80" border="0"></a></td>
    <td width="395" valign="top"><a
    href="album_detail.cfm?cat_id=#cat_info.id#&album_ id=#album_id#"
    class="cat_title">#album#</a>
    <br><a href="" class="cat_title">Listen In Real Audio</a> <IMG
    SRC="images/speaker2.gif" WIDTH="11" HEIGHT="14" BORDER="0" ALT=""
    align="middle"></td>
    <TD align="right" height="10" valign="top" width="275"><B
    class="normal">This Item
    is in stock and ready to ship to you.</B><br>
    <BR>
    <B class="bold">
    <cfoutput group="format">
    <B CLASS="cat_label">#format#</B>&nbsp;<B
    CLASS="cat_price">#DollarFormat(price)# </B>
    <A
    HREF="http://www.1shoppingcart.com/app/netcart.asp?merchantid=8216&amp;productID
    =#shopcartid#" CLASS="cat_add"><IMG ALT="" BORDER="0" HEIGHT="21"
    SRC="images/add_cart.gif" WIDTH="93" align="middle"></A>
    <br>
    </cfoutput>
    </TD>
    <tr><td colspan="3" height="6" valign="middle"><img
    src="images/dashed_line.gif" width="750" height="2"></td></tr>
    </tr>
    </cfoutput>
    </cfoutput>
    </table>


    </TD>
    </TR>
    </TABLE>

    StylusDesigns Guest

Similar Threads

  1. LInked Tables...
    By StylusDesigns in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 4th, 03:56 PM
  2. Connecting to linked SQL tables through access
    By Bonnie in forum ASP Database
    Replies: 4
    Last Post: December 19th, 07:31 PM
  3. Performance with linked tables
    By Eddie Simons in forum ASP Database
    Replies: 1
    Last Post: September 25th, 10:46 AM
  4. problems with linked access tables in ASP
    By Laura in forum ASP Database
    Replies: 6
    Last Post: August 20th, 06:22 PM
  5. Updating via Form to linked tables
    By Dave in forum Microsoft Access
    Replies: 3
    Last Post: August 4th, 05:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •