asked on

Cannot add another inner join to Access 2000 SQL

I have inherited the attached Access 2000 SQL which works fine. However, I need to add the following join statement:

INNER JOIN ItemCategories ON ItemCategories.RefId = Items.CategoryRefID

I then need to add the following statement in the final WHERE clause:

AND ItemCategories.Enable = True

I simply cannot understand how to apply this inner join using the Access SQL syntax. I am fine with SQL server. Any help would be appreciated.

SELECT 
   ItemTexts.Name, 
   t2.rank, 
   t1.stockid, 
   items.*
FROM 
   ( items inner join 
     ( SELECT 
              t1.rank, t1.StockID, pi1.ItemRefID 
       FROM 
              priceditems pi1 
       INNER JOIN 
              ( SELECT 
                        o1.stockid,count(o1.stockid) as rank 
                FROM 
                        orders AS o1 
                WHERE 
                        o1.transactionid IN 
                           ( SELECT DISTINCT 
                                  o2.TransactionID 
                             FROM 
                                  PricedItems pi2 
                                  LEFT JOIN orders o2 ON pi2.StockID = o2.StockID 
                             WHERE ((pi2.ItemRefID=46245974) and o2.transactionid<>'') ) 
                 GROUP BY o1.stockid ) as t1 
         ON pi1.stockid=t1.stockid 
         WHERE 
               pi1.StockStatusRefID <> -1 AND pi1.itemrefid <> 46245974 ) as t2 
       ON items.refID=t2.ItemRefID ) 
      LEFT JOIN itemTexts ON itemtexts.RefId=items.RefID 
WHERE 
     ItemTexts.lang='en' 
     AND Items.Enable=True 
OREDR BY 
     t2.rank desc

Open in new window

GRayL

Did you try looking at the query using the Query Designer - add the table and the join, add the field, ItemsCategory.Enable, uncheck the View checkbox, and add True in the Criteria.

mike99c

ASKER

Hi GRalL,
Unfortunately I am not familiar with the query designer. Do you know what the syntax is to add the extra inner join?

shambalad

Try this:

SELECT itemTexts.Name, t2.rank, t2.stockid, items.*
FROM (((SELECT t1.rank, t1.stockid, pi1.ItemRefID FROM priceditems AS pi1 INNER JOIN [SELECT o1.stockid,count(o1.stockid) as rank FROM orders AS o1 WHERE o1.transactionid IN (SELECT DISTINCT o2.TransactionID FROM PricedItems pi2 LEFT JOIN orders o2 ON pi2.StockID = o2.StockID WHERE ((pi2.ItemRefID=46245974) and o2.transactionid<>'') ) GROUP BY o1.stockid ]. AS t1 ON pi1.StockID = t1.stockid WHERE (((pi1.ItemRefID)<>46245974) AND ((pi1.StockStatusRefID)<>-1))) AS t2
INNER JOIN Items ON t2.ItemRefID = Items.RefID) INNER JOIN ItemCategories ON Items.CategoryRefID = ItemCategories.RefID) LEFT JOIN ItemTexts ON Items.RefID = ItemTexts.RefID;

mike99c

ASKER

Hi Shambalad
Thanks for this. The query does in fact run without any errors and returns results. However when I tried to add the following to the end of the query I got a syntax error.

WHERE
ItemTexts.lang='en'
AND ItemCategories.Enable = True
AND Items.Enable=True
OREDR BY
t2.rank desc

I would appreciate any help in this as the where clause is very important in getting this query to work.

shambalad

By the way, mike99c:
I noticed how you are using RefID as the name of your primary key fields in both the Items and ItemCategories tables. Others may do this differently (What are your thoughts on this, GRayL), but I have found that it makes life much simpler When I use a more descriptive name for my primary keys. For example you could change the name of your primary key in the Items table from RefID to ItemRefID, likewise, the primary key for your ItemCategories table from RefID to CategoryRefID (since this is what you are calling the foreign keys for these tables anyway).
Of course this database may be too far along to think about changing field names, but it's a thought to keep in mind as you develop new tables.

mike99c

ASKER

HI Shambalad,
I agree with your recommendations but unfortunately I inherited the database and query and it will be too much of an impact on the database and related script files for me to make such a change.

Any thoughts on how I could add the reminder of the SQL?

shambalad

Mispelled 'ORDER'

mike99c

ASKER

Hi Shambalad,
Yes I know but that was just a typo when I pasted it in here. When I ran the actual query it was spelt correctly.

ASKER CERTIFIED SOLUTION

shambalad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shambalad

Also added the criteria and the sort

mike99c

ASKER

Hi Shambalad,

Thank you so much. You have found the solution and it works great.

I did not need to change 'en' to "en" and I managed to remove some additional brackets.