[RESOLVED] Not sure where to start

[cbVision]

Hey guys,

I'm not sure how exactly to describe this, but i'll do my best.

I want to pull a list of customers who are tied to one subscription, lets call it SUBSCRIPTION X, but not tied to SUBSCRIPTION Y.

One customer can have many subscriptions. Let's setup a fake database, like this:

customers:
1 John
2 Ted
3 Bill

subscriptions:
1 SUBSCRIPTION X
2 SUBSCRIPTION Y

customer/subscription:
1 1
1 2
2 1
3 1

From this, you can see that John has both subscriptions, and Ted and Bill only have a subscription to X. I'm looking for the query to pull only Bill and Ted.

How the heck does that work?! I think I've just over-thought this thing.

Thanks in advance.

[NogDog]

This is the best I've been able to think of:

SELECT customers.*
FROM customers
INNER JOIN customer_subscription customer_subscription.customer_id = customers.id
WHERE
customers_subscription.subscription_id = 1 AND
customers.id NOT IN(
SELECT customer_id from customer_subscription WHERE subscription_id = 2
)

[cbVision]

Thanks for the response. I'll try going this route!

[NogDog]

I just noticed I left out an "ON":

SELECT customers.*
FROM customers
INNER JOIN customer_subscription ON customer_subscription.customer_id = customers.id
WHERE
customers_subscription.subscription_id = 1 AND
customers.id NOT IN(
SELECT customer_id from customer_subscription WHERE subscription_id = 2
)

[cbVision]

NogDog,

You're solution works like a charm. Thanks!