passing a parameter into a subquery

i was wondering if it is possible to pass a parameter into a select subquery.

What i want to do is collect some product data from one table and then crossref the weight of the item to it's carriage cost in a shipping table to return a cost.

something like:

select cataloguenumber, productname, 
      (select shipping.carriagecost 
       from shipping 
       where shipping.carriageweight = weight) as carriagecost
from pr开发者_开发百科oducts

Regards

DPERROTT

While the subquery would work, a better, more readable and efficient way to define this would be as follows:

SELECT  p.cataloguenumber
 ,      p.productname,  
 ,      s.carriagecost    
FROM    products p
    INNER JOIN
        shipping s
    ON  p.weight = s.carriageweight

This assumes that all product weights have a corresponding entry in the shipping table. If that is not the case then change from INNER JOIN to LEFT JOIN and deal with any nulls.

select cataloguenumber, productname,  shipping.carriagecost as carriagecost 
from products, shipping 
where shipping.carriageweight = products.weight

or am I missing something?

SELECT DISTINCT cataloguenumber, productname, shipping.carriagecost 
FROM products
LEFT OUTER JOIN shipping 
ON shipping.carriageweight = products.weight

Your subquery should only return 1 row, if it returns more then that your query will throw an error in run-time.

This is possible I think, but then you should retrieve the column you want to pass in your parent query.

select cataloguenumber, productname, weight 
      (select shipping.carriagecost 
       from shipping 
       where shipping.carriageweight = weight) as carriagecost
from products

SELECT DISTINCT products.cataloguenumber, products.productname, shipping.carriagecost 
FROM products
LEFT JOIN shipping ON shipping.carriageweight = products.weight