Many to many query w/example download

[cj92713696]

Example -

http://mail.fat-tuesday.com/db.zip

Change 2 to 1 and vice versa on QueryHelp for example
---

I'm having trouble w/a many to many query.

I have 3 tables setup.

Table 1 = Store
Table 2 = (many to many) ManagerStoreAssoc
Table 3 = Manager

Store has 2 fields:
1.) StoreCode (PK)
2.) StoreName

Data: (UPT, Uptown), (ELM, Elmwood)

ManagerStoreAssoc has 3 fields:
1.) ManagerStoreAssocKey (PK)
2.) StoreCode
3.) ManagerKey

Data: (1, UPT, 1)

Manager has 2 fields:
1.) ManagerKey (PK)
2.) ManagerName

Data: (1, "Ray&quot, (2, "Mike&quot

If I specify a ManagerKey of 1, I want the following to appear:

UPT, Uptown, Ray
ELM, Elmwood, <NULL>

If I specify a ManagerKey of 2, I want the following to appear:

UPT, Uptown, <NULL>
ELM, Elmwood, <NULL>

For some reason, when I specify a ManagerKey of 1, I get correct data, however when I specify a ManagerKey of 2, my store - UPT, Uptown is not displayed. What gives?

Any assistance is greatly appreciated!

Thanks,
CJ

---
Example -

http://mail.fat-tuesday.com/db.zip

Change 2 to 1 and vice versa on QueryHelp for example

 

[cj92713696]

This may actually sound a bit silly, but I answered my own question.

The following SQL works w/the above downloadable Access db.

SELECT Store.StoreCode, Store.Store, M.ManagerKey
FROM Store LEFT JOIN [SELECT StoreCode, ManagerKey FROM ManagerStoreAssoc Where ManagerStoreAssoc.ManagerKey = [@ManagerKey]]. AS M ON Store.StoreCode = M.StoreCode
WHERE (((M.ManagerKey)=[@ManagerKey] Or (M.ManagerKey) Is Null));

Is there a better way to write this SQL? I had to use a query inside of a query to get my result to display properly.

Thanks,
CJ

 

[JamesLean]

Try this:

SELECT s.storecode, s.store, m.managername
FROM store s LEFT JOIN (
  managerstoreassoc ms JOIN manager m ON ms.managerkey = m.managerkey AND ms.managerkey = @managerkey
  ) ON s.storecode = ms.storecode

--James