Select statement to join tables?

Anon

Hi,

I'm going crazy over this one!!! Here is some more information:

I have two tables. I want to find out which records have the same subid.

This is what I tried but it didn't work:

$sql = "select members.*,cancels.sub from members,cancels where members.subid=cancels.sub";

Here are my two tables:

CREATE TABLE cancels (
customer varchar(250) NOT NULL default '',
email varchar(250) NOT NULL default '',
sub varchar(20) NOT NULL default '',
KEY sub (sub)
) TYPE=MyISAM;

CREATE TABLE members (
id int(11) NOT NULL auto_increment,
username varchar(25) NOT NULL default '',
password varchar(50) NOT NULL default '',
email varchar(50) NOT NULL default '',
trans varchar(10) default '1',
subid varchar(10) default '1',
active enum('y','n') NOT NULL default 'y',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;

What would the select statement look like?

Any help or advice would be greatly appreciated!

trooper

Tim

This should work

SELECT * from members m, cancels c where m.subid = c.sub

What this does is apply an identifier to each of the tables that is then used later on in the join statement

HTH

Gary Mailer

Anon

Hi,

Thanks for your reply!

I don't know why but that didn't work:
$sql = "SELECT * from members m, cancels c where m.subid = c.sub";

This didn't either:
$sql = "SELECT * from members, cancels where members.subid = cancels.sub";

Any other ideas?

Thanks,

Tim

trooper

whar error do you get?

Anon

It doesn't give an error it just doesn't give any results. I know that members.subid has equals to cancels.sub because I can go in and view them.

$num is equal to zero:

$result = mysql_query($sql)
or die("Couldn't execute query sql.");

$num = mysql_numrows($result);

if ($num == 0) {

BodyHeader("That Subscription # was not found!");
echo "<center><H3>That Subscription # was not found!</h3></center>";
BodyFooter();
exit;
}

Thanks for your help. I've been working on this for 4 days and I'm ready to pull my hair out!

Tim