Help with "Join" statement...

[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 need to figure out which distributors are also 'subscribers' and sort this by EXPIRATION (unix time stamp) putting the ones who expire first at the top.

Started with...
<?
// begin crappy code snippet
$sql = "SELECT * FROM SUBSCRIPTIONS";
$sql_result = mysql_query($sql,$connection) or die ("Couldn't execute query.");

while ($row=mysql_fetch_array($sql_result)) {
$id = $row["ID"];
// now I was gonna kludge another "select" in here
// and echo out the ID NAME and EXPIRATION
// looping through here 250 times select, echo, select, echo
// but this wouldn't be in the order i needed...
// end my crappy code snippet
}

?>

As you can see, I'm a rank amateur. I'd appreciate any help. I think I need to do a "join", but I'm not sure where to begin.

Any help would be appreciated.

--Mark

[LordShryku]

A simple join of the two tables will only pull the data that exists in both tables. So try this for your sql query:

SELECT d.ID,
       d.EXPIRATION 
  FROM DISTRIBUTORS d, SUBSCRIPTIONS s
 WHERE s.ID = d.ID

[elklabone]

Hey, that's exactly what I needed. Thanks a lot, I really appreciate it.

--Mark 🙂 🙂 🙂

[elklabone]

OK... now... how do I pull just the ones out of DISTRIBUTORS who are NOT SUBSCRIBERS?

In other words, I just want the ones out of DISTRIBUTORS where they are NOT listed in SUBSCRIBERS?

I tried modifying this to say

WHERE SUBSCRIBERS.ID != DISTRIBUTORS.ID and I brought my server to it's knees for about 30 seconds and returned over half a million records... it should have only given me 1300.

[LordShryku]

Look up LEFT JOIN in the MySQL manual

[elklabone]

... OK...

I've attempted

... 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.

???

[ultraslacker]

Use NULL to exclude matched rows - if you can't get this, google for OUTER JOIN + NULL.

This thread is along the same lines - http://www.phpbuilder.com/board/showthread.php?s=&threadid=10268623

[elklabone]

Can anyone specify if this is a LEFT JOIN or an OUTER JOIN with a NULL?

I'm being told two different things, and unfortunately, I don't know enough to know who's correct.

😕

[elklabone]

I'm still trying...

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