Help with making less (and cleaner) queries using JOINs

[squark]

Hey all

 

I am getting myself into a big muddle with this. I can do it within code but I really want to pull it as much out of the code as I can so I minimise the amount of queries fired at the database (which is what's happening- its turning something that should be one query into a two queries). When I say muddle- I understand what I want to retrieve but how to go about doing it eludes me at the moment and I keep making messes of statements. If someone kind enough could show me the light I will be so grateful! Maybe it's really simple and I'm over complicating the problem which is why I just need to bounce it off someone. This is after quite a bit of research but honestly, I just need to see something clearly - if it's possible - so I can get what's going on and continue to expand my understanding.

 

Here is the simpliest outline of what I am trying to achieve.

 

I have 2 tables- one is 'user' and one is 'association'.

all this example needs is the follow:

'user' consists of:

userID

 

'association' consists of:

assocationid (which is unique)

userid

area

 

so user looks a little bit like

userid

1

2

3

4

 

and association looks a little bit like

associationid    userid    area

1                          1            1

2                          1            2

3                          1            3

4                          2            1

5                          3            1

 

Put simply

user1 is associated with area 1, 2 and 3

user2 is associated with area 1

user3 is associated with area 1

 

I'm calling up the userid associated with a particular area. That's all sweet- use a JOIN and whack association on the query (WHERE assocation.area = x). So if I wanted all users associated with area1 it would give me user1, user2 and user3.

 

The confusion comes when I want to get a list of users associated with area 1 but also who are also not associated with area 3. That would mean it will spit out user2 and user3 but because user1 is in area1 AND area3 it will skip user1.

Is this a JOIN? I have been trying one join and then a self join of the association table to no avail.

 

I cannot user WHERE assocation.area = x AND association.area <> y on the first JOIN - each association is a single field and it will add the first entry of user1 and ignore the the second entry of user1... fair enough- it is what it's being told to do since it thinks each one is unique, it's just not what I want it to do. How do I make it think right?

 

I am guessing it's another JOIN but how to put it together?

 

Any help would be greatly appreciated!

 

squark

[squark]

Barking up the wrong tree, after days and days of trying to get it to work.

Turns out it's not even JOINs that make it work, it's using subqueries and NOT IN.

 

Thanks to those who had a read over it though!

 

squark