Help with SQL Query syntax

[johngordon12]

Just looking for a bit of help with an SQL query.

The table structure is:

table.Itineraries

ItineraryID, Itinerary, etc

table.Activities

ActivityID, Activity, etc

And an interlinking table

table.ItineraryActivities

ItineraryID, ActivityID

And I have a page here, listing Itineraries:

http://www.goodsafariguide.net/itineraries_beta/index101.php

That I would like to include some of the Activities on.

If it was just the first two tables, and tableActivities had an ItineraryID field, I assume it would be:

SELECT * FROM Itineraries INNER JOIN Activities ON Itineraries.ItineraryID = Activities.ItineraryID

But I'm not sure what the syntax would be to achive the same thing with the interlinking table as well.

Hope that makes sense.

Thanks.

[johngordon12]

I've nearly got it working using:

SELECT * FROM itineraries  INNER JOIN ItineraryActivities ON ItineraryActivities.ItineraryID = itineraries.ItineraryID INNER JOIN activities ON activities.ActivityID = ItineraryActivities.ActivityID WHERE Publish_GSG = 'Yes' AND Category_Order = 1

This is displaying the Acivities I want (in this case countries) here:

http://www.goodsafariguide.net/itineraries_beta/index501.php

Except that its displaying duplicate Itineraries, one for each country.

So all I need to do is get the little table with the countries to repeat within each itinerary, but usint DW's repeat region it doesn't like it because its nested.

Should I be able to somehow add a repeat regiona around:

<table>

      <tr>

           <td><img src="../../itinerary_resources/icons/<?php echo $row_SecurityAssisttradeusers['Icon']; ?>" width="30" height="20" alt="Country Flag" /></td>

           <td><h2><?php echo $row_SecurityAssisttradeusers['Activity']; ?></h2></td>

      </tr>

</table>

[bregent]

Just use a nested loop. The outer loop iterates through the iternerary recordset. That SQL only needs to select from the itererary table. The inner loop passes the itererary id to another sql that joins the link table to the activity table and the resulting recordset contains the related activities that can be displayed. Sounds like this is whay you have in mind, correct? So just plan to code this manually rather than using DW behaviors.