JOIN and limit results

scraff

Hi all,

I have this code:

$query = @mysql_query("
SELECT CDBorders.ordernumber, CDBorderformproducts.productcode
FROM CDBorders
INNER JOIN CDBorderformproducts 
ON CDBorders.ordernumber = CDBorderformproducts.ordernumber
WHERE CDBorderformproducts.sent='0' 
ORDER BY CDBorders.ordernumber LIMIT 3
");

So what it brings up are results where CDBorders.ordernumber = CDBorderformproducts.ordernumber and CDBorderformproducts.sent='0'

So it brings up somthing like this:

ordernumber ----- productcode
1 PC22
1 PC76
2 PC55

What i want is it to bring up THREE results from CDBorders.ordernumber. Still where CDBorderformproducts.sent='0' but not duplicate results from ordernumber.

for example:

ordernumber ----- productcode
1 PC22
2 PC55
5 PC64

So even if ordernumber 1 should have two results from it, i want THREE results from CDBorders.ordernumber

Thankyou

Lars_Berg

SELECT CDBorders.ordernumber, min(CDBorderformproducts.productcode)
FROM CDBorders INNER JOIN CDBorderformproducts
ON CDBorders.ordernumber = CDBorderformproducts.ordernumber
WHERE CDBorderformproducts.sent=0
group by CDBorders.ordernumber
ORDER BY CDBorders.ordernumber
LIMIT 3

scraff

Thankyou very much! thats worked a treat! 😃

scraff

Can i ask you just ONE more thing?? That worked great, but i also want to limit the results further.
I want the same function as the script you gave me but also this:

There is ONE CDBorders.ordernumber to many CDBorderformproducts.productcode.
I need it to give the same results, but ONLY if all the results from CDBorderformproducts.price on one ordernumber is greater than 30.00.

Here is a little example that doesnt work!:

SELECT CDBorders.ordernumber, CDBorderformproducts.productcode
FROM CDBorders INNER JOIN CDBorderformproducts
ON CDBorders.ordernumber = CDBorderformproducts.ordernumber
WHERE CDBorderformproducts.sent=0 AND CDBorderformproducts.sendtime < '$currenttimestamp' AND all(CDBorderformproducts.price > '30.00')
group by CDBorders.ordernumber
ORDER BY CDBorders.ordernumber
LIMIT 3

Thanks!

Lars_Berg

SELECT CDBorders.ordernumber, min(CDBorderformproducts.productcode)
FROM CDBorders INNER JOIN CDBorderformproducts
ON CDBorders.ordernumber = CDBorderformproducts.ordernumber
WHERE CDBorderformproducts.sent=0
group by CDBorders.ordernumber
having min(CDBorderformproducts.price) > 30.00
ORDER BY CDBorders.ordernumber
LIMIT 3