[SOLVED] help with INNER JOIN

[amt925]

Hi all. This is my first post. There are a lot of great things on this forum. Thanks to all who make it happen.

I am having a problem with a query and can't seem to figure it out. I am using an INNER JOIN with a SELECT. I thought I could do this but I am not sure. Here is the query:

 

SELECT p.product_code, p.part_number, p.description, newpricing.price

FROM products p

INNER  JOIN (

SELECT product_code, part_number, custpn

FROM pricing WHERE cust_number = '$customer') AS newpricing ON p.part_number = newpricing.part_number

WHERE ( newpricing.custpn

LIKE  '$searchstring%' )

ORDER  BY newpricing.custpn

 

The error I get is: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT product_code, part_number, custpn FROM pricing WHERE cus"

 

I am running MySQL version 4.0.17 and PHP 4.3.4

 

Any help is greatly appreciated. As I said, I am not even sure if I am able to do the query like this. If I am doing it wrong please don't hesitate to tell me.

Thanks!

AT 

[Illusion]

SELECT p.product_code, p.part_number, p.description, newpricing.price

FROM products  p

INNER  JOIN  pricing  newpricing ON p.part_number = newpricing.part_number

WHERE newpricing.custpn LIKE  '$searchstring%'  AND  newpricing.cust_number = '$customer' ORDER  BY newpricing.custpn;

 

[amt925]

Thanks for the reply, but I already had that working. What I am trying to do is limit the number of records I am searching through.

In detail the pricing table I have is over 1 million records and the products table is always at around 190,000 records. I have different customer numbers in the pricing table since each customer has different pricing. Each part in the products table is in the pricing table for each customer number. What I want to do is get the pricing table records that have the customer number given and inner join the products table with that table. That is why I have the "INNER JOIN (SELECT stuff FROM pricing WHERE cust_number='$customernumber')...

 

The main reason I want to try to get this to work is to cut down the query time.

 

Sorry for not using the code tags before...but I will repost my query and the PHP

 

The MySQL query I am using:

SELECT p.product_code, p.part_number, p.inch_number, p.upc_num, p.weight, p.po_cs_qty, p.mfgpn, p.vendor_name, 
p.description, p.u_m, p.com_avail, p.inch_avail, newpricing.price, newpricing.cust_number, newpricing.custpn 
FROM products p 
INNER JOIN pricing newpricing ON p.part_number = newpricing.part_number 
WHERE newpricing.custpn LIKE '$searchstring%' AND newpricing.cust_number='$currentshopper' ORDER BY newpricing.custpn

 

The PHP I am using:

$partquery = mysql_query("SELECT p.product_code, p.part_number, p.inch_number, p.upc_num, p.weight, p.po_cs_qty, p.mfgpn,
p.vendor_name, p.description, p.u_m, p.com_avail, p.inch_avail, newpricing.price, newpricing.cust_number, newpricing.custpn 
FROM products p 
INNER JOIN pricing newpricing ON p.part_number = newpricing.part_number 
WHERE newpricing.custpn LIKE '$searchstring%' AND newpricing.cust_number='$currentshopper' 
ORDER BY newpricing.custpn LIMIT $pagrem, $max_results");

[amt925]

 

Sorry forgot to change something in the query

 

The MySQL query I am using:

SELECT p.product_code, p.part_number, p.inch_number, p.upc_num, p.weight, p.po_cs_qty, p.mfgpn, p.vendor_name, 
p.description, p.u_m, p.com_avail, p.inch_avail, newpricing.price, newpricing.cust_number, newpricing.custpn 
FROM products p 
INNER JOIN (
SELECT product_code, part_number, custpn
FROM pricing WHERE cust_number = '$currentshopper') newpricing ON p.part_number = newpricing.part_number 
WHERE newpricing.custpn LIKE '$searchstring%' AND newpricing.cust_number='$currentshopper' ORDER BY newpricing.custpn

 

The PHP I am using:

$partquery = mysql_query("SELECT p.product_code, p.part_number, p.inch_number, p.upc_num, p.weight, p.po_cs_qty, p.mfgpn,
p.vendor_name, p.description, p.u_m, p.com_avail, p.inch_avail, newpricing.price, newpricing.cust_number, newpricing.custpn 
FROM products p 
INNER JOIN (
SELECT product_code, part_number, custpn
FROM pricing WHERE cust_number = '$currentshopper') newpricing ON p.part_number = newpricing.part_number 
WHERE newpricing.custpn LIKE '$searchstring%' AND newpricing.cust_number='$currentshopper' 
ORDER BY newpricing.custpn LIMIT $pagrem, $max_results");

[fenway]

No subqueries in <4.1

[amt925]

No subqueries in <4.1

 

Thank you.