Page 1 of 1

some quick joins help

Posted: Wed Jan 17, 2007 1:10 pm
by MrPotatoes
this is the first table:

Code: Select all

CREATE TABLE prices (
	id int(11) NOT NULL auto_increment,
	part varchar(40) NOT NULL,
	jb float NOT NULL,
	wd float NOT NULL,
	margin float NOT NULL,
	price float NOT NULL,
	brand varchar(40) NOT NULL,
	PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
this is the second table:

Code: Select all

CREATE TABLE tempcompare (
	id int(11) NOT NULL auto_increment,
	part varchar(40) NOT NULL,
	jb float NOT NULL,
	wd float NOT NULL,
	price float NOT NULL,
	brand int(11) NOT NULL,
	PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
i wanted to do a compare of this and then put it into another table (that'st he easy part lol).

what i wanted to do was use the brand as the main identifier to make sure that it's the same brand. then compare via the part number and make sure that those are correct and then grab the information from that.

here is my lame code:

Code: Select all

$query =	"SELECT tempcompare.part, prices.part " .
		"FROM prices, tempcompare " .
		"WHERE tempcompare.brand=prices.brand " .
		"AND tempcompare.brand=prices.part"; 

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result))
{
	echo $row['part'] . '<br>';
}
it's not out putting anything.

so, what do i do next? this is the first time that i've used joins and figured this is the way to go.

thanks

Re: some quick joins help

Posted: Wed Jan 17, 2007 1:26 pm
by volka
MrPotatoes wrote:what i wanted to do was use the brand as the main identifier to make sure that it's the same brand.
prices : brand varchar(40)
tempcompare : brand int(11)

Is prices:brand always a number stored as varchar?

Posted: Wed Jan 17, 2007 1:35 pm
by MrPotatoes
good catch. i thought i changed that

Posted: Thu Jan 18, 2007 9:59 am
by mikeq
AND tempcompare.brand=prices.part
so brand is joining on part, is that correct??