two and three selects related problems

Hello all,

I am in the process of using a couple of custom tags cf_twoselectsrelated and cf_threeselectsrelated i found at mm exchange. Which basically allow you to dynamically relate 2 or 3 selects. And as I'm having the same prob with both i thought i'd put them together.

Basically the problem is that when a value is chosen from the first select box the second select displays all values related to all values in the first select, instead of just displaying the values related to the one chosen in the first select. (hope that makes sense).

I thought that this could be due to the query. So I added a INNER JOIN, then and OUTER JOIN just to be sure and i get this error for both: Cannot join on Memo, OLE, or Hyperlink Object ([sc].[category_id]=[c].[category_id]), i dont have any Memo, OLE, or Hyperlink Object in the tables.

While i'm here i'd better check the the way to join 3 tables is:
select * from table1 t1 inner join table2 t2 on t1.id = t2.id inner join table3 t3 on t2.id2 = t3.id2, as i get a syntax error with this one.

any help on this would be appreciated or if you know a better way to relate multiple select please let me know as my hair is starting to fall out on this one.

before i forget i'm using an access db, though am about to migrate to mysql

cheers,

ad

Does the tag come with some sort of example of documentation?

yes, but when i tried it i kept getting errors.

the example was

select * from table t1, table2 t2 where t1.id = t2.id

when i tried that way i kept getting syntax errors so i thought an inner join would work but got a different error... i'm starting to think i have a probelm with my db

Yes, I'd get the example working as they presented it before I did anything further.

well i got the two select working, now with the threeselectrelated ... my second of three select is only showing one record and my third is showing the records for all available in the second... i have come to the conclusion that it is my query .. though dont know where to start...

this the example they gave me ...
SELECT Customers.CustomerID, Customers.Company, Category.ID, Category.Category, Inventory.BookID, Inventory.Title FROM Category, Customers, Inventory, OrderItems, Orders WHERE Category.ID = Inventory.CategoryID AND OrderItems.BookID = Inventory.BookID AND Orders.OrderID = OrderItems.OrderID AND Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerID, Category.ID

this is what i'm doing though i'm using joins, i tried their way and get the same result

SELECT Distinct
m.Market_ID,
m.Market_Name,
z.Zone_ID,
z.Zone_Name,
a.Area_ID,
a.Area_Name
FROM
(
Market m INNER JOIN Zone z ON [m].[market_id] = [z].[market_id]
)
INNER JOIN Area a ON [a].[zone_id] = [z].[zone_id]


cheers