Hi all

I'm stumped with this one. Using PHP 4 and MySQL 3.23. I have two tables:

Products contains the product details which has a field of ProductGroup and I select the data using this field so end up with around 6 records which are all related by this product group. Each item in the group has a price code.

The second table is the price codes, going from A - Z.

I need to SELECT the data which consists of:
ProductName
ProductDescription
ProductPriceCode

The resulting records will be about 6 as mentioned earlier and the prices will show the actual price rather than the price code. So A = 10.00, B = 12.50, etc.

I'm fairly sure a LEFT JOIN can accomplish this but I just can't get it to work. Can anyone help me out on this one?

Many thanks in anticipation,

Jon

If you could post the table structures that would help out a lot...

However the syntax should be something like this:
SELECT ProductName, ProductDescription, ProductPriceCode
FROM Products p INNER JOIN
PriceCode pc ON p.ProductPriceCode = pc.ProductPriceCode
WHERE ....


HTH

Simon

Thanks Simon. Here's the table structures:

CREATE TABLE `pricebands` (
`PriceBandID` int(11) NOT NULL auto_increment,
`pbPriceBand` char(1) NOT NULL default '',
`pbPrice20` decimal(3,2) NOT NULL default '0.00',
`pbPrice30` decimal(3,2) NOT NULL default '0.00',
`pbPrice40` decimal(3,2) NOT NULL default '0.00',
`pbPrice50` decimal(3,2) NOT NULL default '0.00',
`pbPrice60` decimal(3,2) NOT NULL default '0.00',
`pbPrice70` decimal(3,2) NOT NULL default '0.00',
`pbPrice80` decimal(3,2) NOT NULL default '0.00',
`pbPrice90` decimal(3,2) NOT NULL default '0.00',
`pbPrice100` decimal(3,2) NOT NULL default '0.00',
`pbPlus10s` decimal(3,2) NOT NULL default '0.00',
PRIMARY KEY (`PriceBandID`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

CREATE TABLE `products` (
`ProductID` int(11) NOT NULL auto_increment,
`prNumber` int(4) NOT NULL default '0',
`prName` varchar(20) NOT NULL default '',
`prPriceBand` char(1) NOT NULL default '',
`prSize` varchar(10) NOT NULL default '',
PRIMARY KEY (`ProductID`)
) TYPE=MyISAM AUTO_INCREMENT=90 ;

prNumber would have approx. 6 records with the same value so my query would include something like WHERE prNumber = $mynumber.

I hope this makes sense.

Many thanks again

Jon

Hi Jon,

I'm assuming that pbPrice20 - pbPrice100 are for different quantities of the product, price breaks as it were?

In which case if you wanted to get the Product Name, Size etc and also the costs for that product at all quantities I would use something like this:

SELECT pr.prNumber, pr.prSize, pb.*
FROM products pr INNER JOIN pricebands pb ON pr.prPriceBand = pb.PriceBandID
WHERE pr.prNumber = $mynumber

Edited by - ganseki on 21 Jun 2004 12:21:44

FANTASTIC! Many, many thanks. I must have looked at the code for so long my head was hurting. I have all my books open and just couldn't get the JOIN to work.

Thank you for your help. I had to tweak the query slightly but what you gave me sorted out my goofs. Thanks again.

Hi Jon

I've found banging my head on the desk often leads to flashes (of inspiration).
Apologies for the errors in my code (i use sql server and asp) hopefully it was that at fault and not monday morning brain ache

There were no errors in your code, I just needed to change some of the field names. Apart from you changing a table from products to just pr but I spotted that and it's now fully working. 3am is my best time for inspiration but I then forget by morning! Thanks again.