I have three tables--

TBLDIST
I_UUID
STOREDIST_ID
DIST_QTY

TBLUD
UD_USERS_ID
UD_STOREDIST_ID

TBLSTOREDIST
STOREDIST_ID (PK)
STOREDIST_DIST
STOREDIST_DESC
STOREDIST_SCOUNT


TBLSTOREDIST is a list of stores and TBLUD is for each user to have a custom list of stores for their distribution areas. TBLDIST is a list of items and qtys and which dist zone they are shipping to. Each user has their own custom distribution lists but if they edit an item that another user entered who may have a different distribution list they need to see those records as well.

The query I have (which ALMOST works) is:

SELECT * FROM TBLDIST D RIGHT OUTER JOIN TBLSTOREDIST S
ON (D.STOREDIST_ID = S.STOREDIST_ID)
WHERE S.STOREDIST_ID IN (SELECT STOREDIST_ID FROM tblDIST WHERE I_UUID='#URL.I_UUID#&#39 OR
S.STOREDIST_ID IN (SELECT UD_STOREDIST_ID FROM tblUD WHERE UD_Users_ID=#Session.Users_ID#)
ORDER BY S.STOREDIST_DIST ASC

This gives a list of all the distribution zones including any another user may have in their set but I am getting duplicate records for the I_UUID items. It is showing items in the list for both item 1 and item 2 which have the same STOREDIST_ID. If I change the OR to an AND I get only the specific records and can't see the other distribution areas to build a form for inputs.

I have a strong feeling the problem lies in the JOIN but can't figure out where to make the changes.

I tried the following quick and dirty query:

SELECT D.STOREDIST_ID, D.DIST_QTY, D.I_UUID, S.STOREDIST_DIST, S.STOREDIST_DESC
FROM TBLDIST D RIGHT OUTER JOIN TBLSTOREDIST S
ON (D.STOREDIST_ID = S.STOREDIST_ID)
WHERE S.STOREDIST_ID IN (2,3,4,5,6,7,8,9,10,11,12,16,21,28,29,402)

to see where the duplicates were coming from and it is definately in the JOIN.

How can I build my list to get all of the distribution zones (even those with no qty for the item) and make sure that it only brings back the DIST_QTY for that I_UUID?

I've beat my head on this one for a while and can't quite get it.

Thanks for the help.

Todd