Select that finds IF multiple

Question

I am doing a inner join between two tables where one is an association table, so there is a many to one relationship. I am trying to come up with a query that can decide if the key on the join exist more than once than store a value multiple in the update column, but not sure the efficient way to make this happen:

SELECT 
  MainTable.Name 
FROM MainTable 
INNER JOIN ASSN_Main ON MainTable.AppID = ASSN_Main.AppID 
WHERE 
  EXISTS (SELECT 
            COUNT(MainTable.AppID) 
          FROM MainTable 
          INNER JOIN ASSN_Main ON MainTable.AppID = ASSN_Main.AppID 
          GROUP BY 
            MainTable.AppID 
          HAVING 
            (COUNT(MainTable.AppID)>1));

The problem is the subquery grabs the correct ones that have duplicates on the appid, but the main SELECT query grabs all appid names instead of only the ones that exist in the subquery. Not sure whats going wrong since the subquery is correct?

Answer 1

I'm not really sure what you're trying to do, but to debug Access queries in general, I'd break it into multiple queries so you can see what's going on at each step. Then if you want you can combine them into one query that does all the steps.

Answer 2

I don't know access SQL, but something like this would work in SQL Server:

SELECT  
   MainTable.Name  
FROM 
   MainTable  INNER JOIN ASSN_Main ON MainTable.AppID = ASSN_Main.AppID  
WHERE  
   MainTable.AppID IN 
      (SELECT  
          MainTable.AppID  
       FROM MainTable  INNER JOIN ASSN_Main ON MainTable.AppID = ASSN_Main.AppID  
       GROUP BY  
          MainTable.AppID  
       HAVING  
          (COUNT(MainTable.AppID)>1)); 

So basically replacing the EXISTS with IN and return the AppID from the subquery.

Answer 3

Try changing the INNER JOIN to a WHERE clause. When you change it to a WHERE, ASSN_Main in the subquery will refer to the table from the parent query.

There's a good overview of the EXISTS clause here: http://www.techonthenet.com/sql/exists.php

Answer 4

There is no relation between the items in the main query and the items in the subquery, so what the query is returning is "all items, if there are any items that have duplicates". What you want is "all items where there are duplicates":

SELECT 
  MainTable.Name 
FROM MainTable m
INNER JOIN ASSN_Main a ON m.AppID = a.AppID 
WHERE 
  EXISTS (SELECT AppID
          FROM ASSN_Main
          WHERE AppID = m.AppID
          GROUP BY AppID 
          HAVING COUNT(*)>1);