Is there a way inside of Dreamweaver to use the foreign key field of one recordset to access the text label associated with that key from another recordset?

I have a table as follows:
publications {
id_pub
id_pub_category
...
}

And another table defining the category labels:
categories{
id_category
label
}

I have created a recordset for the publications table and am displaying the values in an HTML table. Instead of printing the numeric value of id_pub_category, I would like to do a lookup on the categories table and retrieve the label associated with id_category = id_pub_category.

How would this functionality be implemented in Dreamweaver without using a URL variable?

Thanx tons,

Curtis

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>Is there a way inside of Dreamweaver to use the foreign key field of one recordset to access the text label associated with that key from another recordset?...How would this functionality be implemented in Dreamweaver without using a URL variable?<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
By default, DW doesn't do this on its own, not visually anyway. You can <b>handcode SQL in your <i>Advanced Recordset</i> dialog</b>, and when you click 'Test', then it will run your SQL as long as it's valid. <b>There are extensions to ease your pain</b>, and I'll tell you from owning at least one, it entirely changes your workflow in DW to a higher level of SQL!

Are you familar with <b>JOINs in SQL</b>? Technically this is what you're trying to do - I list articles/links below to handcode/read more.

Links...
<b>MySQL online documentation</b> JOINs - dev.mysql.com/doc/mysql/en/join.html

<b>DMXzone articles</b> on JOINs <ul><li><b>MySQL and Multiple Tables: Part 2</b> - www.dmxzone.com/showDetail.asp?TypeId=28&NewsId=8418</li><li><li><b>Beginner's SQL: UNION</b> - www.dmxzone.com/showDetail.asp?TypeId=28&NewsId=6735</li><b>Beginner's SQL: Cross Joins</b> - www.dmxzone.com/showDetail.asp?TypeId=28&NewsId=6554</li></ul>

<b>Extensions:</b> Personally I use and love, <i>Advanced Query Wizard</i> (www.advancedextensions.com). Another good one is InterAKT's <i>QuB</i> (www.interaktonline.com/Products/Dreamweaver-Extensions/QuB/) (which is probably gonna be upgraded this year) known in MM's DRK9 as <i>"MX Query Builder"</i>.

Both are at least $100 and <u>worth every penny</u>; they work for all <i>Server Models</i> and databases DW supports.

Note: I'm not too sure if AQW works with ImpAKT/NeXTensio/PhaKT (PHP_ADODB) since there may be dialog button conflicts... so email either side and ask before you buy <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

~ ~ ~ ~ ~ ~ ~
Chris Charlton <i>- DMXzone Manager</i>
<font size=1>[ Studio MX/MX2004 | PHP/ASP | SQL | XHTML/CSS | XML | Actionscript | Web Accessibility | MX Extensibility ]</font id=size1>