some quick joins help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MrPotatoes
Forum Regular
Posts: 617
Joined: Wed May 24, 2006 6:42 am

some quick joins help

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: some quick joins help

Post 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?
User avatar
MrPotatoes
Forum Regular
Posts: 617
Joined: Wed May 24, 2006 6:42 am

Post by MrPotatoes »

good catch. i thought i changed that
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

AND tempcompare.brand=prices.part
so brand is joining on part, is that correct??
Post Reply