Help with query

Results 1 to 2 of 2

Thread: Help with query

  1. #1
    Join Date
    Dec 1969

    Default Help with query

    I have a query listed below. <BR>Here is what I have. I have a couple of tables (SQL DB) called MEMBER_DATA, LOCATIONS, and COMMENTS. Each table has a common field member_id. <BR>I want the query to pull a member id from the MEMBER_DATA table and look at the other tables to see if they have data. If Member ID 1 has 10 location entries, and a summary entry, I want it all posted. The member can have unlimited locations (city and state fields) but only one summary. So, Member 1 may be in Nowhere Arizona and Here Alaska and their comments might be "Cool place to be".<BR><BR>Now, I want to display this online with a grouping of those locations in each state then each city. So, Alaska would show first and then a city would be listed and all the member ids would show that have locations in that city. Then the next city would come up and again the member ids would show, then if there were no more cities, the next state would show. <BR><BR>How could I code something like that? Here is my current partial code:<BR><BR>msql = "SELECT member_data.member_category_code AS code, AS city, tsd_locations.state AS state, member_data.member_id AS member_id, " &_<BR> " tsd_comments.summary AS summary, member_data.member_name AS membername, member_data.mailing_addr_1 AS add1, " &_<BR> " member_data.mailing_addr_2 AS add2, member_data.mailing_addr_3 AS add3, member_data.mailing_city AS city1, " &_<BR> " member_data.mailing_state AS state1, member_data.mailing_postal_code AS zip, member_data.mailing_country AS country1, " &_<BR> " AS email, member_data.url AS url, tsd_marketing_tools.grouptravelmanual AS Expr18, " &_<BR> " tsd_marketing_tools.sampleitenerary AS Expr19, tsd_marketing_tools.*, tsd_marketing_tools.colorslides AS Expr5, " &_<BR> " tsd_marketing_tools.bwphotos AS Expr16, tsd_marketing_tools.colorseparations AS Expr17, tsd_marketing_tools.offerscdrom AS Expr20, " &_<BR> " tsd_marketing_tools.brochureshells AS Expr21, tsd_marketing_tools.video_loan AS Expr22, tsd_marketing_tools.video_free AS Expr23, " &_<BR> " tsd_marketing_tools.speakers_bureau AS Expr24, tsd_marketing_tools.slideshow AS Expr25, tsd_marketing_tools.majorevents AS Expr26, " &_<BR> " tsd_marketing_tools.adcoop AS Expr27, tsd_marketing_tools.jointmailing AS Expr28, tsd_marketing_tools.movies AS Expr29, " &_<BR> " tsd_marketing_tools.airfaretosite AS Expr30, tsd_marketing_tools.meals AS Expr31, tsd_marketing_tools.lodging AS Expr32, " &_<BR> " tsd_marketing_tools.groundtrans AS Expr33, tsd_marketing_tools.staffescort AS Expr34" &_<BR> " FROM tsd_locations RIGHT OUTER JOIN" &_<BR> " member_data ON tsd_locations.member_id = member_data.member_id LEFT OUTER JOIN" &_<BR> " tsd_comments ON member_data.member_id = tsd_comments.member_id LEFT OUTER JOIN" &_<BR> " tsd_marketing_tools ON member_data.member_id = tsd_marketing_tools.member_id" &_<BR>" WHERE (LEFT(member_data.member_id, 2) = &#039;AL&#039; OR" &_<BR> " LEFT(member_data.member_id, 2) = &#039;al&#039;) AND (member_data.member_category_code NOT BETWEEN &#039;00&#039; AND &#039;07&#039;) and ( is not null)" &_<BR>" ORDER BY tsd_locations.state,"<BR>Set RS=Conn.Execute(msql)<BR>%&#062;<BR>&#060;!---<BR>&#060;Table border="1" cellpadding="0" cellspacing="0" Align="Center" borderColor="#000000" Background="" &#062;<BR>&#060;TR&#062;<BR>&#060;TD&#062;&#060;Fo nt Face="Arial" Color="#000000"&#062;product_id&#060;/Font&#062;&#060;/TD&#062;<BR><BR> &#060;TD&#062;&#060;Font Face="Arial" Color="#000000"&#062;product_name&#060;/Font&#062;&#060;/TD&#062;<BR><BR> &#060;TD&#062;&#060;Font Face="Arial" Color="#000000"&#062;product_price&#060;/Font&#062;&#060;/TD&#062;<BR><BR><BR>&#060;/TR&#062;<BR>---&#062;<BR>&#060;%<BR>Dim x<BR>x = 0<BR>Do While Not RS.EOF<BR>x = x + 1<BR><BR>catcode=RS("code")<BR>membername=RS("memb ername")<BR>add1=RS("add1")<BR>city1=RS("city1")<B R>state1=RS("state1")<BR>zip=RS("zip")<BR>email=RS ("email")<BR>url=RS("url")<BR><BR>%&#062;

  2. #2
    Join Date
    Dec 1969

    Default Checkout Bill W's FAQ on this..

    The FAQs are at the top of this page. He has one about displayin groups of data that should help you out mucho.<BR><BR>Please don&#039;t flame me because I didn&#039;t tell you exactly what FAQ, copy the URL, or copy the text of the FAQ into a posting. <BR><BR>-Doug

Posting Permissions

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