PHP/MySQL "Join" problem

OK... This is more of a MySQL question...

[elklabone]

I'm struggling here... never really done a SELECT more complicated than WHERE X="Y" before...

I've got two tables.
SUBSCRIPTIONS
DISTRIBUTORS

DISTRIBUTORS has the fields
ID NAME EXPIRATION

SUBSCRIPTIONS just has
ID

... where I record if a DISTRIBUTOR has signed up for a "subscription".

Now I want a list of ONLY the DISTRIBUTORS.ID who ARE NOT listed in SUBSCRIPTIONS. In other words, I have 1600 "distributors", 300 of whom are also "subscribers" and I need a list of the 1300 distributors who ARE NOT "subscribers".

I had someone try to help me earlier, and they suggested I try LEFT JOIN... so I tried:

SELECT * FROM DISTRIBUTORS LEFT JOIN SUBSCRIPTIONS ON DISTRIBUTORS.ID = SUBSCRIPTIONS.ID...

And I'm getting the same 1600 records I started with... it's not excluding the ones from SUBSCRIPTIONS.

I know this is more of a MYSQL question, but I am using PHP to talk to my database. ;-)

Any help is greatly appreciated.

[lorax]

SELECT DISTRIBUTORS.*
FROM DISTRIBUTORS
LEFT JOIN SUBSCRIPTIONS ON DISTRIBUTORS.ID <> SUBSCRIPTIONS.ID

This should find all of the records in DISTRIBUTORS that don't have a match in SUBSCRIPTIONS

[elklabone]

Hmm... something's wacky here.

SELECT DISTRIBUTORS.* FROM DISTRIBUTORS LEFT JOIN SUBSCRIPTIONS ON DISTRIBUTORS.ID <> SUBSCRIPTIONS.ID

Returns like 400,000 records (each duplicated a couple of hundred times), so I tried:

SELECT DISTINCT US_DISTRIBUTORS.* FROM US_DISTRIBUTORS LEFT JOIN SUBSCRIPTIONS ON US_DISTRIBUTORS.ID <> SUBSCRIPTIONS.ID

And it only returns each one once, but I'm still getting all of them instead of just the ones who aren't in the SUBSCRIPTIONS table...

Any ideas? Thanks for your help.

--Mark

[lorax]

Hm...

Ok. So I'm going to ask an obvious question - why is there a seperate table for subscriptions? Why not just have a field in the DISTRIBUTORS table called 'subscribed' or some such?

[Phantom]

or, if you cant figure out the sql, you could sort through the data in php

[andrew_m]

select DISTRIBUTORS.* from DISTRIBUTORS left join SUBSCRIBERS on DISTRIBUTIRS.id=SUBSCRIBERS.id where SUBSCRIBERS.id is null;

That should do the trick for you.

[webadept]

Wow.. really making that base earn it's keep today eh?:-)

Your friend was right, and Andrew's answer will probably be the ticket. Any time you are looking for records in one table, that are not in another (espesially using an id like you are now) it is a LEFT JOIN problem. A LEFT JOIN is similar to a regular join in that it atempts to match rows in the first (left)table with the rows in the second (right) table. But in additin , if a left table row has no match in the right table, a LEFT JOIN still produces a row ... one in which all the columns from the right table are set to NULL.

The reason I'm posting, is that you said:

I've got two tables.
SUBSCRIPTIONS
DISTRIBUTORS

DISTRIBUTORS has the fields
ID NAME EXPIRATION

SUBSCRIPTIONS just has
ID

You have a field there called Expiration? Is that a date? Can a Distributor be a Subscriber if the date is Expired? A null value for that date, is less than whatever date you plug in there. Maybe all you need is a simple SELECT? Looks kind of obvioius, but.. I've had the obvious bite me in the .. well.. you know where.. so many times I don't have to watch what i eat. :-) Just have to keep and eye on what might be trying to eat me.

When you think you've found your answer, use EXLAIN to check through the query as it runs, to see if you are causing more processor than nessessary, or if an index could be tweeked to speed things up. EXPLAIN is a great tool to learn and keep handy. It will definitly keep the brusing down from bangin your head against the keyboard wondering why you are getting back 1600 results from a 1600 record table.

[mysql.com...]

There are a bunch of hints and some very good write-ups on that site. Spend a few hours now and save a ton of time later.